Finding Column Name from all the tables

In some situation the SQL Developers/Administrators required the column name from all the tables in a database. The below query batch will help to find all such columns in the database:

Declare @SearchString nvarchar(100)

SET  @SearchString=‘Search String should be entered here’

CREATE TABLE #Results (TableName nvarchar(370), ColumnName nvarchar(3630))

SET NOCOUNT ON

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

 @SearchString2 nvarchar(110) 

 SET  @TableName =    

 SET @SearchString2 =

 QUOTENAME(‘%’ + @SearchString + ‘%’,””)

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 ‘ + @SearchString2

      )            

      END      

    END    

  END

  SELECT TableName, ColumnName FROM #Results

Advertisements

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

%d bloggers like this: