To search a string in every column of every table in a specific database

To search a string in every column of every table in a specific database. Create the stored procedure on the database that you want to search in.


CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'Base Table'
            And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')


DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                            THEN 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       = ''' + @DataToFind + '''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            ELSE 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       Like ''%' + @DataToFind + '%''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            END,
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i


        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

        SET @i = @i + 1
    END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

To run it just do this:
exec FindMyData_string 'yahoo', 0     --here yahoo is the string name

To get details of MDF and LDF file of a Database

Use [work]   --DB Name
execute sp_helpfile

Stored Procedure to Generate Drop Create Reference Key

sp_GenDropCreateRefsFkScript


USE [Work] --DB Name
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GenDropCreateRefsFkScript]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GenDropCreateRefsFkScript]
GO


USE [Work]  --DB Name
GO
CREATE PROCEDURE [dbo].[sp_GenDropCreateRefsFkScript]
    @SCHEMA_NAME VARCHAR(10),
    @TABLE_NAME VARCHAR(50),  
    @DROPSQL VARCHAR(max) OUTPUT,
    @CREATESQL VARCHAR(max) OUTPUT
    
AS   


DECLARE  
   @FK_NAME SYSNAME,  
   @FK_OBJECTID INT,  
   @FK_DISABLED INT,  
   @FK_NOT_FOR_REPLICATION INT,  
   @DELETE_RULE    smallint,     
   @UPDATE_RULE    smallint,     
   @FKTABLE_NAME SYSNAME,  
   @FKTABLE_OWNER SYSNAME,  
   @PKTABLE_NAME SYSNAME,  
   @PKTABLE_OWNER SYSNAME,  
   @FKCOLUMN_NAME SYSNAME,  
   @PKCOLUMN_NAME SYSNAME,  
   @CONSTRAINT_COLID INT, 
   
   @DROPCND VARCHAR(MAX),
   @CREATECND VARCHAR(MAX)
    
DECLARE CSR_FKS CURSOR FOR   
   SELECT  fk.name,  
           fk.OBJECT_ID,   
           fk.is_disabled,   
           fk.is_not_for_replication,   
           fk.delete_referential_action,   
           fk.update_referential_action,   
           OBJECT_NAME(fk.parent_object_id) AS fk_table_name,   
           schema_name(fk.schema_id) AS fk_table_schema,   
           tbs.name AS pk_table_name,   
           schema_name(tbs.schema_id) pk_table_schema  
   FROM    sys.foreign_keys fk LEFT OUTER JOIN   
           sys.tables tbs ON tbs.OBJECT_ID = fk.referenced_object_id  
   WHERE   tbs.name = @TABLE_NAME 
           AND schema_name(tbs.schema_id) = @SCHEMA_NAME


SET @DROPSQL = ''
SET @CREATESQL = ''


OPEN CSR_FKS  
       
WHILE 1 = 1  
BEGIN
   FETCH NEXT FROM CSR_FKS   
   INTO @FK_NAME,@FK_OBJECTID,  
       @FK_DISABLED,  
       @FK_NOT_FOR_REPLICATION,  
       @DELETE_RULE,     
       @UPDATE_RULE,     
       @FKTABLE_NAME,  
       @FKTABLE_OWNER,  
       @PKTABLE_NAME,  
       @PKTABLE_OWNER
   IF @@FETCH_STATUS <> 0
  BREAK 
   -- preparing drop statement 
   BEGIN 
  SET @DROPCND = 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' 
+ @FK_NAME + ']'') AND parent_object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' 
+ @FKTABLE_NAME + ']''))' + CHAR(10) + 'BEGIN' + CHAR(10)
       SET @DROPSQL = @DROPSQL + @DROPCND
       SET @DROPSQL = @DROPSQL + 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
       + ']  DROP CONSTRAINT [' + @FK_NAME + '];' + CHAR(10) + 'END;' + CHAR(10)
 END 
  
   -- preparing recreate statement 
 BEGIN 
  
       DECLARE 
@FKCOLUMNS VARCHAR(1000), 
@PKCOLUMNS VARCHAR(1000), 
@COUNTER INT

       -- get Foreign Key columns  
       DECLARE CSR_FKCOLS CURSOR FOR 
       SELECT  COL_NAME(fk.parent_object_id, fkc.parent_column_id) AS fk_col_name,   
               COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) AS pk_col_name  
       FROM    sys.foreign_keys fk LEFT OUTER JOIN   
               sys.tables tbs ON tbs.OBJECT_ID = fk.referenced_object_id INNER JOIN   
               sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.OBJECT_ID   
       WHERE   tbs.name = @TABLE_NAME  
               AND schema_name(tbs.schema_id) = @SCHEMA_NAME 
               AND fkc.constraint_object_id = @FK_OBJECTID  
       ORDER BY fkc.constraint_column_id  
       OPEN CSR_FKCOLS  
       
       SET @COUNTER = 1  
       SET @FKCOLUMNS = ''  
       SET @PKCOLUMNS = ''  
         
       WHILE 1 = 1   
       BEGIN
           FETCH NEXT FROM  CSR_FKCOLS INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
           IF @@FETCH_STATUS <> 0
               BREAK
               
           IF @COUNTER > 1   
           BEGIN  
               SET @FKCOLUMNS = @FKCOLUMNS + ','  
               SET @PKCOLUMNS = @PKCOLUMNS + ','  
           END  
           SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'  
           SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'  
           SET @COUNTER = @COUNTER + 1  
           
       END  
       CLOSE CSR_FKCOLS   
       DEALLOCATE CSR_FKCOLS   
  
  SET @CREATECND = 'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' + @FK_NAME + ']'') AND parent_object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']''))' + CHAR(10) + 'BEGIN' + CHAR(10)
       SET @CREATESQL = @CREATESQL + @CREATECND + 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  WITH ' +   
           CASE @FK_DISABLED   
               WHEN 0 THEN ' CHECK '  
               WHEN 1 THEN ' NOCHECK '  
           END +  ' ADD CONSTRAINT [' + @FK_NAME   
           + '] FOREIGN KEY (' + @FKCOLUMNS   
           + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('   
           + @PKCOLUMNS + ') ON UPDATE ' +   
           CASE @UPDATE_RULE   
               WHEN 0 THEN ' NO ACTION '  
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET NULL '   
               END + ' ON DELETE ' +   
           CASE @DELETE_RULE  
               WHEN 0 THEN ' NO ACTION '   
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET NULL '   
               END + '' +  
           CASE @FK_NOT_FOR_REPLICATION  
               WHEN 0 THEN ''  
               WHEN 1 THEN ' NOT FOR REPLICATION '  
           END  
           + ';' + CHAR(10) + 'END;' + CHAR(10)


   END
END  
CLOSE CSR_FKS   
DEALLOCATE CSR_FKS
GO

Stored Procedure to Generate Drop Create Primary Key

sp_GenDropCreatePkScript.



USE [Work]  --DB Name
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GenDropCreatePkScript]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GenDropCreatePkScript]
GO


USE [Work]  --DB Name
GO
CREATE PROCEDURE [dbo].[sp_GenDropCreatePkScript](
@SCHEMA_NAME VARCHAR(10),
    @TABLE_NAME VARCHAR(50),  
    @DROPSQL VARCHAR(max) OUTPUT,
    @CREATESQL VARCHAR(max) OUTPUT
)
AS


DECLARE
   @KEY_NAME SYSNAME,
   @INDEX_ID SMALLINT,   
   @TYPE SMALLINT,
   @TYPE_DESC VARCHAR(20),
   @UNIQUE VARCHAR(6),
   @DATA_SPACE SYSNAME,
   @STAT_NOCOMPUTE VARCHAR(3),
   @IGNORE_DUP_KEY VARCHAR(3), 
   @IS_PRIMARY_KEY SMALLINT,
   @IS_UNIQUE_CONSTRAINT SMALLINT,
   @FILL_FACTOR VARCHAR(20),
   @IS_PADDED VARCHAR(3),
   @IS_DISABLED SMALLINT,
   @ALLOW_ROW_LOCKS VARCHAR(3),
   @ALLOW_PAGE_LOCKS VARCHAR(3),
   
   @DROPCND VARCHAR(MAX),
   @CREATECND VARCHAR(MAX),
   @IDXCOLUMN_NAME SYSNAME,
   @ORDER VARCHAR(10),
   @INCLUDED SMALLINT,
   @IDXCOLUMNS SYSNAME,
   @INCCOLUMNS SYSNAME,
   
   @ON VARCHAR(2) = 'ON',
   @OFF VARCHAR(3) = 'OFF'


DECLARE CSR_PKS CURSOR FOR
SELECT idx.name
 ,idx.type
 ,idx.index_id
      ,idx.type_desc
      ,CASE idx.is_unique
WHEN 0 THEN ''
WHEN 1 THEN 'UNIQUE'
  END
      ,ds.name
      ,CASE st.no_recompute
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,CASE idx.ignore_dup_key
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,idx.is_unique_constraint
      ,CASE idx.fill_factor
WHEN 0 THEN ''
ELSE ', FILLFACTOR = ' + convert(VARCHAR,idx.fill_factor)
  END
      ,CASE idx.is_padded
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,idx.is_disabled
      ,CASE idx.allow_row_locks
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,CASE idx.allow_page_locks
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
  FROM sys.indexes idx LEFT OUTER JOIN
sys.tables tbs ON idx.object_id = tbs.object_id AND idx.name IS NOT NULL LEFT OUTER JOIN
sys.data_spaces ds on idx.data_space_id = ds.data_space_id LEFT OUTER JOIN
sys.stats st on st.object_id = idx.object_id AND st.name = idx.name
  WHERE tbs.name = @TABLE_NAME
AND idx.is_primary_key = 1
AND schema_name(tbs.schema_id) = @SCHEMA_NAME
  ORDER BY idx.is_unique_constraint DESC


SET @DROPSQL = ''
SET @CREATESQL = ''


OPEN CSR_PKS  
       
WHILE 1 = 1  
BEGIN
   FETCH NEXT FROM CSR_PKS   
   INTO @KEY_NAME   
   ,@TYPE
   ,@INDEX_ID
   ,@TYPE_DESC
   ,@UNIQUE
   ,@DATA_SPACE
   ,@STAT_NOCOMPUTE
   ,@IGNORE_DUP_KEY
   ,@IS_UNIQUE_CONSTRAINT
   ,@FILL_FACTOR
   ,@IS_PADDED
   ,@IS_DISABLED
   ,@ALLOW_ROW_LOCKS
   ,@ALLOW_PAGE_LOCKS
   
   IF @@FETCH_STATUS <> 0
BREAK
 
-- preparing drop statement 


   SET @DROPCND = 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(''[' + @SCHEMA_NAME + '].[' + @TABLE_NAME +']'') AND name = ''' + @KEY_NAME + ''')' + CHAR(10) + 'BEGIN' +  CHAR(10)
   SET @DROPSQL = @DROPSQL + @DROPCND


   SET @DROPSQL = @DROPSQL + 'ALTER TABLE [' + @SCHEMA_NAME + '].[' + @TABLE_NAME   
       + '] DROP CONSTRAINT [' + @KEY_NAME + '];' + CHAR(10) + 'END;' + CHAR(10)

-- preparing recreate statement 
   BEGIN
       DECLARE 
  @COUNTER INT,
  @INCLUDECNT INT,
  @TMP VARCHAR(MAX)
  
       -- get Foreign Key columns  
       DECLARE CSR_PKCOLS CURSOR FOR 
       SELECT  COL_NAME(ic.object_id, ic.column_id) AS pk_col_name,
  CASE is_descending_key
WHEN 0 THEN 'ASC'
WHEN 1 THEN 'DESC'
  END,
  ic.is_included_column
       FROM    sys.indexes idx LEFT OUTER JOIN   
               sys.tables tbs ON tbs.OBJECT_ID = idx.object_id INNER JOIN   
               sys.index_columns ic ON ic.object_id = idx.object_id and ic.index_id = idx.index_id 
       WHERE   tbs.name = @TABLE_NAME 
               AND schema_name(tbs.schema_id) = @SCHEMA_NAME 
               AND ic.index_id = @INDEX_ID  
       ORDER BY ic.index_column_id, ic.key_ordinal
       
       OPEN CSR_PKCOLS  
       
       SET @COUNTER = 1
       SET @INCLUDECNT = 1
       SET @IDXCOLUMNS = ''
       SET @INCCOLUMNS = ''
         
       WHILE 1 = 1   
       BEGIN
  FETCH NEXT FROM  CSR_PKCOLS INTO @IDXCOLUMN_NAME, @ORDER, @INCLUDED
       
           IF @@FETCH_STATUS <> 0
      BREAK
      
  IF (@INCLUDED = 0)
  BEGIN
  IF @COUNTER > 1    
  SET @IDXCOLUMNS = @IDXCOLUMNS + ', ' 
          
  SET @IDXCOLUMNS = @IDXCOLUMNS + '[' + @IDXCOLUMN_NAME + '] ' + @ORDER  
  SET @COUNTER = @COUNTER + 1
  END
  ELSE
  BEGIN
 IF @INCLUDECNT > 1    
  SET @INCCOLUMNS = @INCCOLUMNS + ', ' 
          
  SET @INCCOLUMNS = @INCCOLUMNS + '[' + @IDXCOLUMN_NAME + ']' 
  SET @INCLUDECNT = @INCLUDECNT + 1
  END
       END
       CLOSE CSR_PKCOLS   
       DEALLOCATE CSR_PKCOLS
       
       SET @CREATECND = 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(''[' + @SCHEMA_NAME + '].[' + @TABLE_NAME +']'') AND name = ''' + @KEY_NAME + ''')' + CHAR(10) + 'BEGIN' +  CHAR(10)
       SET @TMP = @CREATECND


  SET @TMP = @TMP + 'ALTER TABLE [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '] ADD CONSTRAINT [' + @KEY_NAME + '] PRIMARY KEY ' + @TYPE_DESC   
       SET @TMP = @TMP + ' ( '+ @IDXCOLUMNS + ') '
       
       IF (LEN(@INCCOLUMNS)>0)
   SET @TMP = @TMP + 'INCLUDE (' + @INCCOLUMNS + ') '
   
       SET @TMP = @TMP + 'WITH ('
       + 'PAD_INDEX  = ' + @IS_PADDED
       + ', STATISTICS_NORECOMPUTE  = ' + @STAT_NOCOMPUTE
       + ', SORT_IN_TEMPDB = OFF'
       + ', IGNORE_DUP_KEY = ' + @IGNORE_DUP_KEY
       --+ ', DROP_EXISTING = OFF'
       + ', ONLINE = OFF'
       + ', ALLOW_ROW_LOCKS  = ' + @ALLOW_ROW_LOCKS
       + ', ALLOW_PAGE_LOCKS  = ' + @ALLOW_PAGE_LOCKS
       + @FILL_FACTOR
       + ') ON [' + @DATA_SPACE
       + '];' + CHAR(10)
       
       SET @TMP = @TMP + 'END;' + CHAR(10)
       SET @CREATESQL = @CREATESQL + @TMP


    END
END
CLOSE CSR_PKS
DEALLOCATE CSR_PKS
GO

Stored Procedure to Generate Drop and Create Index

sp_GenDropCreateIndexScript.


USE [Work]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GenDropCreateIndexScript]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GenDropCreateIndexScript]
GO


USE [Work]
GO
CREATE PROCEDURE [dbo].[sp_GenDropCreateIndexScript](
@SCHEMA_NAME VARCHAR(10),
    @TABLE_NAME VARCHAR(50),  
    @DROPSQL VARCHAR(max) OUTPUT,
    @CREATESQL VARCHAR(max) OUTPUT
)
AS


DECLARE
   @KEY_NAME SYSNAME,
   @INDEX_ID SMALLINT,   
   @TYPE SMALLINT,
   @TYPE_DESC VARCHAR(20),
   @UNIQUE VARCHAR(6),
   @DATA_SPACE SYSNAME,
   @STAT_NOCOMPUTE VARCHAR(3),
   @IGNORE_DUP_KEY VARCHAR(3), 
   @IS_PRIMARY_KEY SMALLINT,
   @IS_UNIQUE_CONSTRAINT SMALLINT,
   @FILL_FACTOR VARCHAR(20),
   @IS_PADDED VARCHAR(3),
   @IS_DISABLED SMALLINT,
   @ALLOW_ROW_LOCKS VARCHAR(3),
   @ALLOW_PAGE_LOCKS VARCHAR(3),
   
   @DROPCND VARCHAR(MAX),
   @CREATECND VARCHAR(MAX),
   @IDXCOLUMN_NAME SYSNAME,
   @ORDER VARCHAR(10),
   @INCLUDED SMALLINT,
   @IDXCOLUMNS SYSNAME,
   @INCCOLUMNS SYSNAME,
   
   @ON VARCHAR(2) = 'ON',
   @OFF VARCHAR(3) = 'OFF'


DECLARE CSR_IDXS CURSOR FOR
SELECT idx.name
 ,idx.type
 ,idx.index_id
      ,idx.type_desc
      ,CASE idx.is_unique
WHEN 0 THEN ''
WHEN 1 THEN 'UNIQUE'
  END
      ,ds.name
      ,CASE st.no_recompute
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,CASE idx.ignore_dup_key
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,idx.is_primary_key
      ,idx.is_unique_constraint
      ,CASE idx.fill_factor
WHEN 0 THEN ''
ELSE ', FILLFACTOR = ' + convert(VARCHAR,idx.fill_factor)
  END
      ,CASE idx.is_padded
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,idx.is_disabled
      ,CASE idx.allow_row_locks
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,CASE idx.allow_page_locks
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
  FROM sys.indexes idx LEFT OUTER JOIN
sys.tables tbs ON idx.object_id = tbs.object_id AND idx.name IS NOT NULL LEFT OUTER JOIN
sys.data_spaces ds on idx.data_space_id = ds.data_space_id LEFT OUTER JOIN
sys.stats st on st.object_id = idx.object_id AND st.name = idx.name
  WHERE idx.is_primary_key = 0
AND tbs.name = @TABLE_NAME
AND schema_name(tbs.schema_id) = @SCHEMA_NAME
  ORDER BY idx.is_unique_constraint DESC


SET @DROPSQL = ''
SET @CREATESQL = ''


OPEN CSR_IDXS
       
WHILE 1 = 1   
BEGIN
   FETCH NEXT FROM CSR_IDXS   
   INTO @KEY_NAME   
   ,@TYPE
   ,@INDEX_ID
   ,@TYPE_DESC
   ,@UNIQUE
   ,@DATA_SPACE
   ,@STAT_NOCOMPUTE
   ,@IGNORE_DUP_KEY
   ,@IS_PRIMARY_KEY
   ,@IS_UNIQUE_CONSTRAINT
   ,@FILL_FACTOR
   ,@IS_PADDED
   ,@IS_DISABLED
   ,@ALLOW_ROW_LOCKS
   ,@ALLOW_PAGE_LOCKS
   
   IF @@FETCH_STATUS <> 0
BREAK

-- preparing drop statement 
   BEGIN
    SET @DROPCND = 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(''[' + @SCHEMA_NAME + '].[' + @TABLE_NAME +']'') AND name = ''' + @KEY_NAME + ''')' + CHAR(10) + 'BEGIN' +  CHAR(10)
SET @DROPSQL = @DROPSQL + @DROPCND

IF (@IS_UNIQUE_CONSTRAINT = 1)
       SET @DROPSQL = @DROPSQL + 'ALTER TABLE [' + @SCHEMA_NAME + '].[' + @TABLE_NAME   
       + '] DROP CONSTRAINT [' + @KEY_NAME + '];' + CHAR(10) + 'END;' + CHAR(10)
    ELSE
  SET @DROPSQL = @DROPSQL + 'DROP INDEX [' + @KEY_NAME +'] ON [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '] WITH ( ONLINE = OFF );' + CHAR(10) + 'END;' + CHAR(10)
   END

-- preparing recreate statement 
   BEGIN
       DECLARE 
  @COUNTER INT,
  @INCLUDECNT INT,
  @TMP VARCHAR(MAX)
  
       -- get Foreign Key columns  
       DECLARE CSR_IDXCOLS CURSOR FOR 
       SELECT  COL_NAME(ic.object_id, ic.column_id) AS idx_col_name,
  CASE is_descending_key
WHEN 0 THEN 'ASC'
WHEN 1 THEN 'DESC'
  END,
  ic.is_included_column
       FROM    sys.indexes idx LEFT OUTER JOIN   
               sys.tables tbs ON tbs.OBJECT_ID = idx.object_id INNER JOIN   
               sys.index_columns ic ON ic.object_id = idx.object_id and ic.index_id = idx.index_id 
       WHERE   tbs.name = @TABLE_NAME 
               AND schema_name(tbs.schema_id) = @SCHEMA_NAME 
               AND ic.index_id = @INDEX_ID  
       ORDER BY ic.index_column_id, ic.key_ordinal 
       
       OPEN CSR_IDXCOLS  
       
       SET @COUNTER = 1
       SET @INCLUDECNT = 1
       SET @IDXCOLUMNS = ''
       SET @INCCOLUMNS = ''
         
       WHILE 1 = 1   
       BEGIN
  FETCH NEXT FROM  CSR_IDXCOLS INTO @IDXCOLUMN_NAME, @ORDER, @INCLUDED
  IF @@FETCH_STATUS <> 0
  BREAK
  
  IF (@INCLUDED = 0)
  BEGIN
  IF @COUNTER > 1    
  SET @IDXCOLUMNS = @IDXCOLUMNS + ', ' 
          
  SET @IDXCOLUMNS = @IDXCOLUMNS + '[' + @IDXCOLUMN_NAME + '] ' + @ORDER  
  SET @COUNTER = @COUNTER + 1
  END
  ELSE
  BEGIN
 IF @INCLUDECNT > 1    
  SET @INCCOLUMNS = @INCCOLUMNS + ', ' 
          
  SET @INCCOLUMNS = @INCCOLUMNS + '[' + @IDXCOLUMN_NAME + ']' 
  SET @INCLUDECNT = @INCLUDECNT + 1
  END


       END
       CLOSE CSR_IDXCOLS   
       DEALLOCATE CSR_IDXCOLS
       
       SET @CREATECND = 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(''[' + @SCHEMA_NAME + '].[' + @TABLE_NAME +']'') AND name = ''' + @KEY_NAME + ''')' + CHAR(10) + 'BEGIN' +  CHAR(10)
       SET @TMP = @CREATECND
       
       IF (@IS_UNIQUE_CONSTRAINT = 1)
   SET @TMP = @TMP + 'ALTER TABLE [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '] ADD CONSTRAINT [' + @KEY_NAME + '] UNIQUE ' + @TYPE_DESC
       ELSE
SET @TMP = @TMP + 'CREATE ' + @UNIQUE + ' ' + @TYPE_DESC + ' INDEX [' + @KEY_NAME + '] ON [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + ']'
       
       SET @TMP = @TMP + ' ( '+ @IDXCOLUMNS + ') '
       
       IF (LEN(@INCCOLUMNS)>0)
   SET @TMP = @TMP + 'INCLUDE (' + @INCCOLUMNS + ') '
   
       SET @TMP = @TMP + 'WITH ('
       + 'PAD_INDEX  = ' + @IS_PADDED
       + ', STATISTICS_NORECOMPUTE  = ' + @STAT_NOCOMPUTE
       + ', SORT_IN_TEMPDB = OFF'
       + ', IGNORE_DUP_KEY = ' + @IGNORE_DUP_KEY
       --+ ', DROP_EXISTING = OFF'
       + ', ONLINE = OFF'
       + ', ALLOW_ROW_LOCKS  = ' + @ALLOW_ROW_LOCKS
       + ', ALLOW_PAGE_LOCKS  = ' + @ALLOW_PAGE_LOCKS
       + @FILL_FACTOR
       + ') ON [' + @DATA_SPACE
       + '];' + CHAR(10)
       
       --statement to set index disabled
       IF (@IS_DISABLED = 1)
 SET @TMP = @TMP 
 + 'ALTER INDEX [' + @KEY_NAME
 + '] ON [' + @SCHEMA_NAME
 + '].[' + @TABLE_NAME
 + '] DISABLE;' + CHAR(10)
         
       SET @TMP = @TMP + 'END;' + CHAR(10)
       SET @CREATESQL = @CREATESQL + @TMP


    END


END
CLOSE CSR_IDXS   
DEALLOCATE CSR_IDXS
GO

Stored Procedure to Generate Drop Create Foreign Key Script

sp_GenDropCreateFkScript


USE [Work]  --DB Name
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GenDropCreateFkScript]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GenDropCreateFkScript]
GO


USE [Work]  --DB Name
GO
CREATE PROCEDURE [dbo].[sp_GenDropCreateFkScript]
    @SCHEMA_NAME VARCHAR(10),
    @TABLE_NAME VARCHAR(50),  
    @DROPSQL VARCHAR(max) OUTPUT,
    @CREATESQL VARCHAR(max) OUTPUT
    
AS   


DECLARE  
   @FK_NAME SYSNAME,  
   @FK_OBJECTID INT,  
   @FK_DISABLED INT,  
   @FK_NOT_FOR_REPLICATION INT,  
   @DELETE_RULE    smallint,     
   @UPDATE_RULE    smallint,     
   @FKTABLE_NAME SYSNAME,  
   @FKTABLE_OWNER SYSNAME,  
   @PKTABLE_NAME SYSNAME,  
   @PKTABLE_OWNER SYSNAME,  
   @FKCOLUMN_NAME SYSNAME,  
   @PKCOLUMN_NAME SYSNAME,  
   @CONSTRAINT_COLID INT, 
   
   @DROPCND VARCHAR(MAX),
   @CREATECND VARCHAR(MAX)
    
DECLARE CSR_FKS CURSOR FOR   
   SELECT  fk.name,  
           fk.OBJECT_ID,   
           fk.is_disabled,   
           fk.is_not_for_replication,   
           fk.delete_referential_action,   
           fk.update_referential_action,   
           OBJECT_NAME(fk.parent_object_id) AS fk_table_name,   
           schema_name(fk.schema_id) AS fk_table_schema,   
           tbs1.name AS pk_table_name,   
           schema_name(tbs1.schema_id) pk_table_schema  
   FROM    sys.foreign_keys fk LEFT OUTER JOIN   
           sys.tables tbs ON tbs.OBJECT_ID = fk.parent_object_id LEFT OUTER JOIN
           sys.tables tbs1 ON tbs1.OBJECT_ID = fk.referenced_object_id
   WHERE   tbs.name = @TABLE_NAME 
           AND schema_name(tbs.schema_id) = @SCHEMA_NAME


SET @DROPSQL = ''
SET @CREATESQL = ''


OPEN CSR_FKS  
       
WHILE 1 = 1 
BEGIN   
   FETCH NEXT FROM   CSR_FKS   
   INTO @FK_NAME,@FK_OBJECTID,  
       @FK_DISABLED,  
       @FK_NOT_FOR_REPLICATION,  
       @DELETE_RULE,     
       @UPDATE_RULE,     
       @FKTABLE_NAME,  
       @FKTABLE_OWNER,  
       @PKTABLE_NAME,  
       @PKTABLE_OWNER
   IF @@FETCH_STATUS <> 0
  BREAK  
   -- preparing drop statement 
   BEGIN 
  SET @DROPCND = 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' 
+ @FK_NAME + ']'') AND parent_object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' 
+ @FKTABLE_NAME + ']''))' + CHAR(10) + 'BEGIN' + CHAR(10)
       SET @DROPSQL = @DROPSQL + @DROPCND
       SET @DROPSQL = @DROPSQL + 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
       + ']  DROP CONSTRAINT [' + @FK_NAME + '];' + CHAR(10) + 'END;' + CHAR(10)
 END 
  
   -- preparing recreate statement 
 BEGIN 
  
       DECLARE 
@FKCOLUMNS VARCHAR(1000), 
@PKCOLUMNS VARCHAR(1000), 
@COUNTER INT

       -- get Foreign Key columns  
       DECLARE CSR_FKCOLS CURSOR FOR 
       SELECT  COL_NAME(fk.parent_object_id, fkc.parent_column_id) AS fk_col_name,   
               COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) AS pk_col_name  
       FROM    sys.foreign_keys fk LEFT OUTER JOIN   
               sys.tables tbs ON tbs.OBJECT_ID = fk.parent_object_id INNER JOIN   
               sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.OBJECT_ID   
       WHERE   tbs.name = @TABLE_NAME  
               AND schema_name(tbs.schema_id) = @SCHEMA_NAME 
               AND fkc.constraint_object_id = @FK_OBJECTID  
       ORDER BY fkc.constraint_column_id  
       OPEN CSR_FKCOLS  
       
       SET @COUNTER = 1  
       SET @FKCOLUMNS = ''  
       SET @PKCOLUMNS = ''  
         
       WHILE 1 = 1  
       BEGIN   
           FETCH NEXT FROM  CSR_FKCOLS INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
           IF @@FETCH_STATUS <> 0
  BREAK
   
           IF @COUNTER > 1   
           BEGIN  
               SET @FKCOLUMNS = @FKCOLUMNS + ','  
               SET @PKCOLUMNS = @PKCOLUMNS + ','  
           END  
           SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'  
           SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'  
           SET @COUNTER = @COUNTER + 1  
             
       END  
       CLOSE CSR_FKCOLS   
       DEALLOCATE CSR_FKCOLS   
  
  SET @CREATECND = 'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' + @FK_NAME + ']'') AND parent_object_id = OBJECT_ID(''[' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']''))' + CHAR(10) + 'BEGIN' + CHAR(10)
       SET @CREATESQL = @CREATESQL + @CREATECND + 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  WITH ' +   
           CASE @FK_DISABLED   
               WHEN 0 THEN ' CHECK '  
               WHEN 1 THEN ' NOCHECK '  
           END +  ' ADD CONSTRAINT [' + @FK_NAME   
           + '] FOREIGN KEY (' + @FKCOLUMNS   
           + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('   
           + @PKCOLUMNS + ') ON UPDATE ' +   
           CASE @UPDATE_RULE   
               WHEN 0 THEN ' NO ACTION '  
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET NULL '   
               END + ' ON DELETE ' +   
           CASE @DELETE_RULE  
               WHEN 0 THEN ' NO ACTION '   
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET NULL '   
               END + '' +  
           CASE @FK_NOT_FOR_REPLICATION  
               WHEN 0 THEN ''  
               WHEN 1 THEN ' NOT FOR REPLICATION '  
           END
           + ';' + CHAR(10) + 'END;' + CHAR(10)


   END  
END  
CLOSE CSR_FKS   
DEALLOCATE CSR_FKS
GO