Constrint

CONSTRAINTS USING CREATE COMMAND
Constraints is a mechanism used by oracle to prevent invalid
data entry into the table.

CONSTRAINTS ARE DECLARED AS

AT COLUMN LEVEL
COLUMN DATA_TYPE CONSTRAINT CONSTRAINT_NAME CONSTRAINT_TYPE

AT TABLE LEVEL
CONSTRAINT CONSTRAINT_NAME CONSTRAINT_TYPE(COLUMN NAME)

after the last column

 e.g. -1 (COLUMN LEVEL)

CREATE TABLE ADDRESS
(
NAME VARCHAR(30) CONSTRAINT NA_CONS NOT NULL;
);

e.g. – 2 (TABLE LEVEL)

CREATE TABLE ADDRESS
(
NAME VARCHAR(30), ADDRESS VARCHAR(30),
CONSTRAINT NA_P PRIMARY KEY(NAME)
);

Constraint_name is used to refer that constraints further.
for e.g. to delete a constraint, we require the constraint name.
Constraint name may option. If it is option SQL gives a name to
the constraint. The constraint name is in the format

SYS_Cn

where n is a 5 digit number. SQL gives a unique number
to each constraints. We cannot understand clearly this constraint
name refers to which column. So better to give a name to each
constraint when it will create.

The Constraint names are stored in the SQL table
USER_CONSTRAINTS. So we can see all constraint names that are
already created from this table.

STRUCTURE OF USER_CONSTRAINTS ARE :

OWNER NOT NULL VARCHAR2(30);
CONSTRAINT_NAME NOT NULL VARCHAR2(30);
CONSTRAINT_TYPE VARCHAR2(1);
TABLE_NAME NOT NULL VARCHAR2(30);
SEARCH_CONDITION LONG;
R_OWNER VARCHAR2(30);
R_CONSTRAINT_NAME VARCHAR2(30);
DELETE_RULE VARCHAR2(9);
STATUS VARCHAR2(8);

e.g -1. to display all constraints.

SELECT * FROM USER_CONSTRAINTS;

e.g. – 2 to display specific table constraints

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME =
‘ADDRESS’;

 CONSTRAINTS ARE THREE TYPES :

  1.  Domain integrity constraints
    This constraints maintain values according to the
    specifications like

    1.  NOT NULL – the user has to enter a value for the
      column. (Default column constraint is
      NULL that allow NULL value).

note : NULL value not equal to 0.
e.g.
CREATE TABLE ADDRESS
(
NAME VARCHAR(20) CONSTRAINT NA_NULL NOT NULL,
ADDRESS VARCHAR(20),
CITY VARCHAR(20) CONSTRAINT CT_NULL NOT NULL,
PINCODE NUMBER(6)
);

  1.  CHECK – Check input values to the check condition.

e.g.

CREATE TABLE STUDENTS
(
NAME VARCHAR(20),
STANDARD NUMBER(2),
SECTION VARCHAR(1),
TAMIL NUMBER(3) CONSTRAINT T_C CHECK (TAMIL >= 0 AND
TAMIL <= 100), ENGLISH NUMBER(3) CONSTRAINT E_C CHECK (ENGLISH >= 0
AND ENGLISH <= 100), MATHS NUMBER(3) CONSTRAINT M_C CHECK (MATHS >= 0
AND MATHS <= 100)
);

2. Entity integrity constraint

Maintains uniqueness in a record.

(i) UNIQUE – Use to prevent the duplication of values.

e.g.

CREATE TABLE DAYBOOK

(
VNO NUMBER(5) CONSTRAINT VNO_U UNIQUE,
DT DATE,
PARTICULAR VARCHAR(30),
DCODE NUMBER(5),
CCODE NUMBER(5),
AMOUNT NUMBER(12,2)
);

(ii) PRIMARY KEY – Avoid duplication of values as well as
not allow NULL values.
e.g.
CREATE TABLE BLOCK
(
BL_CODE VARCHAR(2) CONSTRAINT BL_C_P PRIMARY KEY,
BL_NAME VARCHAR(30)
);

3. Referential integrity constraint

Enforces, relationship between tables.

FOREIGN KEY(COLUMN) REFERENCES TABLE(COLUMN)
belongs to this type.

e.g.

Table – 1 (Parent Table)

CREATE TABLE MASTER
(
CODE NUMBER(5) CONSTRAINT CD_P PRIMARY KEY,
HEAD VARCHAR(30),
ADDRESS VARCHAR(30),
TELEPHONE VARCHAR(30)
);

Table -2 (Child table) (AT COLUMN LEVEL)

CREATE TABLE DAYBOOK
(
VNO NUMBER(5) UNIQUE,
DT DATE,
PARTICULAR VARCHAR(30),
AMOUNT NUMBER(12,2),
DCODE NUMBER(5) CONSTRAINT DC_F REFERENCES
BLOCK(BL_CODE),
CCODE NUMBER(5) CONSTRAINT CC_D REFERENCES
BLOCK(BL_CODE),
);

OR (AT TABLE LEVEL)
CREATE TABLE DAYBOOK
(
VNO NUMBER(5) UNIQUE,
DT DATE,
PARTICULAR VARCHAR(30),
AMOUNT NUMBER(12,2),
DCODE NUMBER(5),
CCODE NUMBER(5),
CONSTRAINT DC_F FOREIGN KEY(DCODE) REFERENCES
BLOCK(BL_CODE),
CONSTRAINT CC_F FOREIGN KEY(CCODE) REFERENCES
BLOCK(BL_CODE)
);

NOTE :
The references key of the Parent table
should be a primary key or unique.

ON DELETE CASCADE

This option used with a references key. If this option
is used, then a row is deleted from the Parent table,
then related rows from the child tables will also deleted.

e.g. above

FOREIGN KEY(DCODE) REFERENCES BLOCK(BL_CODE) ON DELETE
CASCADE,

COMPOSITE KEYS

More than one column can be used in a constraint
declaration, then this constraint will refer both column in the
table.
e.g.
CREATE TABLE DAYBOOK

(
VNO NUMBER(5),
DT DATE,
AMOUNT NUMBER(10),
CONSTRAINT AMT_DT_U UNIQUE (AMOUNT, DT)
);

9. CONSTRAINTS USING ALTER COMMANDS

We can add, modify,delete, enable or disable constraints by
using ALTER TABLE command.

1. Add constraints

ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME
CONSTRAINT_TYPE (COLUMN);
e.g.
ALTER TABLE ADDRESS ADD CONSTRAINT NAME_U UNIQUE(NAME);

2. Modify NULL/NOT NULL Constraints.

ALTER TABLE TABLE_NAME MODIFY (COLUMN DATATYPE NULL/NOT
NULL);
e.g.

ALTER TABLE ADDRESS MODIFY (NAME VARCHAR(20) NOT NULL);

3. Delete constraints

ALTER TABLE TABLE_NAME DROP CONSTRAINT_NAME;
e.g.
ALTER TABLE ADDRESS DROP NAME_U;

4. Enable / Disable

To Enable or Disable any constraints. Default Enable.

ALTER TABLE TABLE_NAME ENABLE/DISABLE CONSTRAINT
CONSTRAINT_NAME;

Once constraints are disable then that particular
constraint cannot active.

10. DEFAULT VALUE SETTING

We can set default value to a column using default clause.

COLUMN DATA_TYPE DEFAULT VALUE.

e.g.

TAMIL NUMBER(3) DEFAULT 35.

Here if no value given to the column tamil then, default
value to be set.

e.g.-1

CREATE TABLE STUDENT
(
NAME VARCHAR2(30),
STD NUMBER(2),
SEC VARCHAR2(1),
TAMIL NUMBER(3) DEFAULT 35,
ENGLISH NUMBER(3) DEFAULT 35,
MATHS NUMBER(3) DEFAULT 35
);

INSERT INTO STUDENT (NAME,STD,SEC,ENGLISH,MATHS)
VALUES (‘RAJA’,6,’C’,60,60);

Here, Tamil mark not given, so it sets default value 35.

Advertisements

PACKAGE

PACKAGE

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Package is compiled and stored in database that can be used later.

PL/SQL package basically have two components.

  • Package Specification
  • Package Body

Package Specification :

Specification is the interface to the package. It consists of a declaration of all the variables, constants, cursors, types, procedures, functions, and exceptions that can be referenced from outside the package.

The elements which are all declared declared in the specification are called public elements. Any subprogram not in the package specification but coded in the package body is called a private element.

Syntax :

CREATE [OR REPLACE] PACKAGE < package name >
IS
< sub programs and element declaration>
END < package name >;

Example
CREATE PACKAGE emp_sal AS 
 PROCEDURE find_sal(e_id emp.id%type); 
END emp_sal; 

Package Body

The body holds implementation details and private declarations, which are hidden from code outside the package (can be called only from inside the package).

It should contain implementation for all the subprograms/cursors that have been declared in the specification.

It can also have more subprograms or other elements that are not declared in specification. These are called private elements.

It is a dependable object, and it depends on package specification.

The state of the package body becomes ‘Invalid’ every time when the specification is compiled. Therefore, it needs to be recompiled each time after the compilation of specification.

Syntax :

CREATE [OR REPLACE] PACKAGE BODY < package name >
IS
< global_declaration part >
< private element definition >
< sub programs and element declaration>
< Package Initialization >

END < package name >;

Example :
CREATE OR REPLACE PACKAGE BODY emp_sal AS 
 
 PROCEDURE find_sal(e_id emp.id%TYPE) IS 
 e_sal emp.salary%TYPE; 
 BEGIN 
 SELECT salary INTO e_sal 
 FROM customers 
 WHERE id = e_id; 
 dbms_output.put_line('Salary: '|| e_sal); 
 END find_sal; 
END emp_sal; 
/

Referencing Package Contents

To reference the types, items, subprograms, and call specs declared within a package spec, use dot notation:

package_name.type_name
package_name.item_name
package_name.subprogram_name

Example :

DECLARE 
 id emp.id%type := &e_id; 
BEGIN 
 emp_sal.find_sal(id); 
END; 
/

Trigger

Trigger

  • A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table.
  • A trigger is triggered automatically when an associated DML statement is executed

Syntax

 CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }  {
INSERT [OR] | UPDATE [OR] | DELETE}  [OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
BEGIN    
          — sql statements   
END;

  • CREATE [OR REPLACE ] TRIGGER trigger_name – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • {BEFORE | AFTER | INSTEAD OF– This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} – This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
  • [OF col_name– This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
  • [ON table_name– This clause identifies the name of the table or view to which the trigger is associated.
  • [REFERENCING OLD AS o NEW AS n– This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
  • [FOR EACH ROW– This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
  • WHEN (condition) This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

Type of PL/SQL Trigger

There are two types of triggers based on the which level it is triggered.
1) Row level trigger – An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger – An event is triggered for each sql statement executed. 

  1. PL/SQL Trigger Execution Hierarchy
    The following hierarchy is followed when a trigger is fired.
    1) BEFORE statement trigger fires first.
    2) Next BEFORE row level trigger fires, once for each row affected.
    3) Then AFTER row level trigger fires once for each affected row. This events will alternates between BEFORE and AFTER row level triggers.
    4) Finally the AFTER statement level trigger fires.

For Example: Let’s create a table ‘product_check’ which we can use to store messages when triggers are fired.

CREATE TABLE product(Message varchar2(50),  Current_Date number(32));

Let’s create a BEFORE and AFTER statement and row level triggers for the product table.

1) BEFORE UPDATE, Statement Level: This trigger will insert a record into the table ‘product_check’ before a sql update statement is executed, at the statement level.

CREATE or REPLACE TRIGGER Before_Update_Stat_product BEFORE UPDATE ON product Begin INSERT INTO product_check Values(‘Before update, statement level’,sysdate); END; /

2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table ‘product_check’ before each row is updated.

 CREATE or REPLACE TRIGGER Before_Upddate_Row_product  BEFORE  UPDATE ON product  FOR EACH ROW  BEGIN  INSERT INTO product_check  Values(‘Before update row level’,sysdate);  END;  /

3) AFTER UPDATE, Statement Level: This trigger will insert a record into the table ‘product_check’ after a sql update statement is executed, at the statement level.

 CREATE or REPLACE TRIGGER After_Update_Stat_product  AFTER  UPDATE ON product  BEGIN  INSERT INTO product_check  Values(‘After update, statement level’, sysdate);  End;  /

4) AFTER UPDATE, Row Level: This trigger will insert a record into the table ‘product_check’ after each row is updated.

 CREATE or REPLACE TRIGGER After_Update_Row_product  AFTER   insert On product  FOR EACH ROW  BEGIN  INSERT INTO product_check  Values(‘After update, Row level’,sysdate);  END;  /

Now lets execute a update statement on table product.

 UPDATE PRODUCT SET unit_price = 800   WHERE product_id in (100,101);

Lets check the data in ‘product_check’ table to see the order in which the trigger is fired.

 SELECT * FROM product_check;

Output:

Mesage                                             Current_Date

————————————————————

Before update, statement level          26-Nov-2008
Before update, row level                    26-Nov-2008
After update, Row level                     26-Nov-2008
Before update, row level                    26-Nov-2008
After update, Row level                     26-Nov-2008
After update, statement level            26-Nov-2008

The above result shows ‘before update’ and ‘after update’ row level events have occured twice, since two records were updated. But ‘before update’ and ‘after update’ statement level events are fired only once per sql statement.

 

The above rules apply similarly for INSERT and DELETE statements.

How To know Information about Triggers.

We can use the data dictionary view ‘USER_TRIGGERS’ to obtain information about any trigger.

The below statement shows the structure of the view ‘USER_TRIGGERS’

 DESC USER_TRIGGERS;

NAME                              Type

——————————————————–

TRIGGER_NAME                 VARCHAR2(30)
TRIGGER_TYPE                  VARCHAR2(16)
TRIGGER_EVENT                VARCHAR2(75)
TABLE_OWNER                  VARCHAR2(30)
BASE_OBJECT_TYPE           VARCHAR2(16)
TABLE_NAME                     VARCHAR2(30)
COLUMN_NAME                  VARCHAR2(4000)
REFERENCING_NAMES        VARCHAR2(128)
WHEN_CLAUSE                  VARCHAR2(4000)
STATUS                            VARCHAR2(8)
DESCRIPTION                    VARCHAR2(4000)
ACTION_TYPE                   VARCHAR2(11)
TRIGGER_BODY                 LONG

This view stores information about header and body of the trigger.

SELECT * FROM user_triggers WHERE trigger_name = ‘Before_Update_Stat_product’; The above sql query provides the header and body of the trigger ‘Before_Update_Stat_product’

Script Maintain

Script prepare is very hard to maintain. because its changing day by day requirement. all modification save in query in version wise.  very important things is script should not be error. so we have write object not exists condition included in the script.

Type of Script

  • DDL Script  —create trigger  create table, alter table , delete table. its make easy to preparing script. otherwise import the source database and find the missing colum using this query

select t.name,c.name from designdb.sys.table t inner join designdb.sys.column c on t.object_id=c.object.id

except

select t.name,c.namefrom source.sys.table t inner join source.sys.column c on t.object_id=c.object.id

delete script

After Insert records, we update so many things. we not remember sometime. after executing time we know what missing. so that particular table we have delete with condition query.

Insert script:

To create insert query it take lot of time. because we make insert before check its already exist or not. after that we need to insert. in Generate script its little confused what are table we inserted. we cross check and make insert query its take time but we don’t have confident all table inserted or not. I found a anomalous block to make insert query given table name. its very easy to make insert query.  I have alter that sp and update the maximum three condition if not exists given code then insert the query. suppose its exists its will update. so this query run  in source db and run in designation db.

Update script

Some master table wont be delete or insert. because more foregin key tables include. this type of table we will update directly

view script

udt script

sp scripts

 

IDENTITY

IDENTITY

  • Its a Auto Increment Numeric Value
  • Identity columns can be int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0
  • you can’t add identity in existing column. you should delete and add column with identity
  • when error occur in Stored procedure identity value is not rollback in insert query.

Example:

CREATE TABLE dbo.Yaks ( YakID smallint identity(7,2), YakName char(20) )
(7,2)  -- its means Start from 7 and Increment with 2

Identity Column no need specified on time of Insert. 

INSERT INTO dbo.Yaks (YakName) values (‘Gertrude’)
INSERT INTO dbo.Yaks (YakName) values (‘Helga’)

SELECT YakID, YakName FROM dbo.Yaks

YakID YakName
—— ——————–
7        Gertrude
9          Helga

How to Get last Identity value after Insert

INSERT INTO dbo.Yaks (YakName) values (‘Sam the Yak’)
SELECT SCOPE_IDENTITY() as NewRec

Suppose you will Insert Identity Column manually then you will use bellow code

SET IDENTITY_INSERT Yaks ON

Example

SET IDENTITY_INSERT Yaks ON
INSERT INTO dbo.Yaks (YakID, YakName) Values(1, ‘Mac the Yak’)
SET IDENTITY_INSERT Yaks OFF

If you delete all the records from a table it won’t reset the identity.

DELETE FROM dbo.Yaks
INSERT INTO dbo.Yaks (YakName) Values (‘New Herd’)
SELECT SCOPE_IDENTITY()

To reset the identity seed you need to use a DBCC command.

DELETE FROM dbo.Yaks
DBCC CHECKIDENT(‘Yaks’, RESEED, 7)
INSERT INTO dbo.Yaks (YakName) Values (‘New Herd’)
SELECT SCOPE_IDENTITY()

*************************************************************************************

Edit Command

SQL Editing commands

  1.  A[ppend]
    • select * from emp append where deptno=10;
    • select * from emp where deptno=10;
  2.  c[hange] – change /old[/new]
    • eg: select * from emp where deptno=10; c / deptno=10/job=’clerk’ select * from emp where job=’clerk’
  3.  Ed[it] –edit filename[.ext]
  4.  I[nput] – like append but can give multiple lines
    • input <text>
    • eg:  select * from emp i where sal>2000 or deptno=20;
    •           select * from emp where sal>2000 or deptno=20;
  5.  del –del [n]
    1. eg: select 2. ename, 3. deptno,job 4. from emp
    2.        del 2 select  deptno,job from emp
  6.  list  –l[ist] [n|n m|n *|n last|*|* n|* last|last]
    • n- lists line n n m – lists lines n through m
  7.  save –save the contents of the sql buffer in a specified file
    • save filename [cre[ate]|rep[lace]|app[end]]
    • To clear the contents in the buffer give
    • clear buffer;
  8.  run –Lists and executes the currently stored command in the buffer
    • r[un]   (or)  / (slash) – Executes the current command without displaying it
  9.  @   –@ filename[.ext] Executes the contents of the specified file.
  10.  start  –start filename[.ext]
    • same as @
  11.  get –get filename[.ext]
    • The contents of specified file will be brought to the buffer
  12.  spool –spo[ol] [filename[.ext] |off]
    • Stores the query results to a file.

 

Date Function – sql server

DECLARE @DATE DATETIME =GETDATE()

SELECT Dateadd(D,1,@DATE) ADDDATE,
 DATEDIFF(D,@DATE,@DATE+10) DTDIFF,
 DATENAME(D,@DATE) DTNM,
 DATEPART(WW,@DATE) DTPART,
 GETUTCDATE() UTCDT,
 Day(@DATE) AS DD,
 MONTH(@DATE) AS MM,
 YEAR(@DATE) AS YY

OUTPUT
ADDDATE                 DTDIFF      DTNM                          DTPART   UTCDT                   DD          MM           YY
----------------------- ----------- ------------------------------ ------  ----------------------- ----------- ----------- -----------
2017-11-21 15:52:03.633 10           20                            47      2017-11-20 10:22:03.633 20           11         2017

Continue reading

Notes Database

SQL SERVER

 

SQL SERVER MANAGEMENT STUDIO (SSMS)

 

TITLE Page No
CREATE DATABASE

ALTER DATABASE

RENAME DATABASE

DROP DATABASE

CREATE SCHEMA

ALTER SCHEMA

DROP SCHEMA

CREATE ROLE

ALTER ROLE

DROP ROLE

CREATE LOGIN

ALTER LOGIN

DROP LOGIN

CREATE VIEW

ALTER VIEW

DROP VIEW

CREATE TABLE

TYPE OF DATATYPE

ALTER TABLE

RENAME TABLE

DROP TABLE

USING SELECT STATEMENT CREATE TABLE

TYPES OF CONSTRAINTS

CREATE CONSTRAINTS

ALTER CONSTRAINTS

DROP CONSTRAINTS

CREATE CLUSTER INDEX

CREATE NON CLUSTER INDEX

INSERT RECORDS

INSERT RECORDS USING SELECT STATEMENT

USING SELECT INSERT TABLE

UPDATE RECORDS

UPDATE RECORDS USING JOIN

MERGE JOIN

DELETE RECORDS

DELETE RECORDS USING JOIN

TRUNCATE TABLE

DIFFERENCE BETWEEN DELETE AND TRUNCATE

DELETE DUPLICATE RECORDS

JOIN

SET

SUB QUERIES

MULTI ROW SUB QUERIES

[> ALL] More than the highest value returned by the subquery

[< ALL] Less than the lowest value returned by the subquery

[< ANY] Less than the highest value returned by the subquery

[> ANY] More than the lowest value returned by the subquery

[= ANY] Equal to any value returned by the subquery (same as IN)

CORRELATED SUB QUERY

GROUP BY

HAVING

AGGREGATE FUNCTION

NUMERIC FUNCTION

STRING FUNCTION

DATE FUNCTION

ANALYTICAL FUNCTION

 

 

 

DATABASE
Purpose
Syntax

CREATE

ALTER

RENAME

DROP

CREATE DATABASE <DatabaseName>ALTER DATABASE <DatabaseName>

ALTER DATABASE oldName MODIFY NAME = newName

DROP DATABASE <DatabaseName>

Example
Schema
Purpose Schema is use to separate the group of objects and its used schema wise Login user given rights
Syntax CREATE Schema <Schema name>

ALTER

DROP

Example
Notes Once drop all objects in schema only you can able to drop schema
Role
Purpose
Syntax
Example
Login
Purpose
Syntax
Example
Create

 

Search a column data in all table

DECLARE @SearchStr nvarchar(max)
SET @SearchStr = 'OACM'
 
    CREATE TABLE #Results (ColumnName nvarchar(max), ColumnValue nvarchar(max))
 DECLARE @StrResult VARCHAR(MAX)
    SET NOCOUNT ON
 
    DECLARE @TableName nvarchar(max), @ColumnName nvarchar(max), @SearchStr2 nvarchar(max)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
    WHILE @TableName IS NOT NULL
     
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )
 
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
             
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1) AND TABLE_NAME NOT LIKE '%$%'
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
     
            IF @ColumnName IS NOT NULL
             
            BEGIN
			Set @StrResult='SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
					PRINT @StrResult
                INSERT INTO #Results
                EXEC(@StrResult)
                --(
                --    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                --    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                --)
				
				
            END
			
        END 
		
    END
 

DROP TABLE #Results