Search entire SQL Server database and all tables for a specified string

-- =============================================

-- Author:        <Jainul Khan>

-- Create date: <11-12-2014>

-- Description: <Search entire SQL Server database and all tables for a specified string>

-- =============================================

-- [dbo].[sp_SearchFromAllTables] 'Entered'

CREATE PROCEDURE sp_SearchFromAllTables ( @SearchStr nvarchar(100) )

AS

BEGIN

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

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 DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

                AND QUOTENAME(COLUMN_NAME) > @ColumnName

        )

        IF @ColumnName IS NOT NULL

        BEGIN

            INSERT INTO @Results

            EXEC

            (

                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

                FROM ' + @TableName + ' (NOLOCK) ' +

                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

            )

        END

    END

END

SELECT ColumnName, ColumnValue FROM @Results

END

Extended Date Formats in SQL Server

Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.

Extended Date Formats
Date Format	SQL Statement	Sample Output
YY-MM-DD	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]	99-01-24
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]	1999-01-24
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]	08/99
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	12/2005
YY/MM	SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	99/08
YYYY/MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2005/12
Month DD, YYYY 1	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 20061
Mon YYYY 1	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006 1
Month YYYY1	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 20061
DD Month 1	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September1
Month DD 1	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 111
DD Month YY 1	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 72 1
DD Month YYYY 1	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002 1
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]	12/92
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	05-2006
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]	92/12
YYYY-MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2006-05
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252006
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072002
Mon-YY 1	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02 1
Mon-YYYY 1	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002 1
DD-Mon-YY1	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05 1
DD-Mon-YYYY 1	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-20051

¹ To make the month name in upper case, simply use the UPPER string function. 

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