Find and Replace Values in All Tables and All Text Columns using SQL Server

DECLARE @stringToFind VARCHAR(100)

DECLARE @stringToReplace VARCHAR(100)

DECLARE @schema sysname

DECLARE @table sysname

DECLARE @count INT

DECLARE @sqlCommand VARCHAR(8000)

DECLARE @where VARCHAR(8000)

DECLARE @columnName sysname

DECLARE @object_id INT

                    

SET @stringToFind = 'Smith'

SET @stringToReplace = 'Jones'

                       

DECLARE TAB_CURSOR CURSOR  FOR

SELECT   B.NAME      AS SCHEMANAME,

         A.NAME      AS TABLENAME,

         A.OBJECT_ID

FROM     sys.objects A

         INNER JOIN sys.schemas B

           ON A.SCHEMA_ID = B.SCHEMA_ID

WHERE    TYPE = 'U'

ORDER BY 1

         

OPEN TAB_CURSOR

FETCH NEXT FROM TAB_CURSOR

INTO @schema,

     @table,

     @object_id

     

WHILE @@FETCH_STATUS = 0

  BEGIN

    DECLARE COL_CURSOR CURSOR FOR

    SELECT A.NAME

    FROM   sys.columns A

           INNER JOIN sys.types B

             ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID

    WHERE  OBJECT_ID = @object_id

           AND IS_COMPUTED = 0

           AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')

    OPEN COL_CURSOR

    

    FETCH NEXT FROM COL_CURSOR

    INTO @columnName

    

    WHILE @@FETCH_STATUS = 0

      BEGIN

        SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName

                           + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),'''

                           + @stringToFind + ''',''' + @stringToReplace + ''')'

        

        SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''

        

        EXEC( @sqlCommand + @where)

        

        SET @count = @@ROWCOUNT

        

        IF @count > 0

          BEGIN

            PRINT @sqlCommand + @where

            PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)

            PRINT '----------------------------------------------------'

          END

        

        FETCH NEXT FROM COL_CURSOR

        INTO @columnName

      END

    

    CLOSE COL_CURSOR

    DEALLOCATE COL_CURSOR

    

    FETCH NEXT FROM TAB_CURSOR

    INTO @schema,

         @table,

         @object_id

  END

  

CLOSE TAB_CURSOR

DEALLOCATE TAB_CURSOR

Find Stored Procedure Containing by Table Name

SELECT DISTINCT t.name [TableName], p.name [ProcedureName]

FROM sys.objects t

LEFT JOIN sys.sql_dependencies d ON

    d.referenced_major_id = t.object_id

LEFT JOIN sys.objects p ON

    p.object_id = d.object_id  

WHERE t.type = 'u'

and t.name like '%tbl_Order%'

Find Tables in a Database Who Have a Filed With Same Name

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'

FROM sys.tables t

     INNER JOIN sys.columns c

        ON c.object_id = t.object_id

WHERE c.name like '%OrderID%'

ORDER BY 'Table Name'

Find and Delete Duplicate Rows From a Table in SQL Server

WITH CTE AS(

   SELECT *,     

   RN = ROW_NUMBER()OVER(PARTITION BY OrderID ORDER BY OrderID)

   FROM tbl_Order  

)

SELECT * FROM CTE WHERE RN >1

DELETE   FROM CTE WHERE RN >1