Ravendra's Blog

February 10, 2010

SQL Query to find all column names in a table

Filed under: Uncategorized — Ravendra Mishra @ 12:19 pm

 

declare @SearchStr as
varchar(256)

set @SearchStr =
‘System_’

 

CREATE
TABLE #Results
(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
distinct (ColumnName)
FROM #Results order
by ColumnName–where columnName like ‘%Words%’

drop
table #Results

Advertisements

1 Comment »

  1. WOW just what I was looking for. Came here by searching for blog

    Comment by favorisiert.com — August 5, 2014 @ 7:51 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: