Querying Database for Column Names (version 2.0)

I created a previous blog on how to query the Database for column names are returning the tables. This has been very effective in reverse engineering a client database that is based on Microsoft SQL Server. I have added an update to his query that also returns the number of rows with the table.







In this example, we are looking for a column with the name "staff" in the column name.

------ --------------------------------------------

----- FIND TABLES With a specific Column Name ----
-------------------------------------------------
---- Change the Variable @FindColumn with  ------
-----the Column name your are looking for  ------
----- in the Database                      ------
-------------------------------------------------

DECLARE @FindColumn VARCHAR(64)

SET @FindColumn = '%staff%';

IF OBJECT_ID('tempdb..#counts') IS NOT NULL DROP TABLE #counts

IF OBJECT_ID('tempdb..#table_row_count') IS NOT NULL DROP TABLE #table_row_count

CREATE TABLE #counts (table_name VARCHAR(255),row_count INT )

 

EXEC sp_MSForEachTable @command1 = 'INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT LEFT(RIGHT(table_name, LEN(table_name) - 7), LEN(RIGHT(table_name, LEN(table_name) - 7)) - 1) table_name  ,row_count

INTO #table_row_count FROM #counts ORDER BY 1;

WITH RESULTS

AS (

       SELECT t.NAME AS TableName

              ,[type_desc] [Type]
              ,SCHEMA_NAME(schema_id) AS [Schema]

              ,c.NAME AS ColumnName

       FROM sys.tables AS t

       INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

       WHERE c.NAME LIKE @FindColumn      

   )

SELECT RS.TableName

       ,RS.[Type]

       ,RS.ColumnName

       ,TC.row_count

FROM RESULTS RS

LEFT JOIN #table_row_count TC ON TC.table_name collate SQL_Latin1_General_CP1_CI_AS = RS.TableName ORDER BY [Schema]    ,TableName