Finding columns and tables

Querying the sys, tables in a database can make looking for references to objects or listing out columns a very simple task. Whilst the tables being queried hold a lot of interesting and useful information it’s generally necessary to be specific about the information that you view from them if you want your output to be reasonably meaningful.

Note. In the examples below I’ll use parameters to keep the code easy to work with and I’ll be filtering out objects that are flagged as is_ms_shipped to keep the results clean.

Listing a tables columns

To list the columns on a single or group of tables the following code can be used:

DECLARE @TableName AS VARCHAR(250)
SET @TableName = 'Persons'
 
SELECT  o.name TableName
, c.name ColumnName
, max_length
, c1.DATA_TYPE
FROM sys.objects o
JOIN sys.columns c ON c.object_id = o.object_id
					AND o.is_ms_shipped <> 1
JOIN INFORMATION_SCHEMA.COLUMNS c1 ON c1.TABLE_NAME = o.name
				   AND c1.COLUMN_NAME = c.name
WHERE
--o.name like '%' + @TableName + '%'
o.name = @TableName

Enter the name of the table for which you’d like your columns listed and execute. To change from and exact match on the table name comment out the bottom line with two hyphens and uncomment the line above.  The results for the query above could look like this:

Listing tables with columns

Finding a list of columns and their tables with a similar name can be very useful if all of the keys are properly named for that the following code can be used:

DECLARE @ColumnName AS VARCHAR(250)
SET @ColumnName = 'name'
 
SELECT  o.name TableName
, c.name ColumnName
, max_length
, c1.DATA_TYPE
FROM sys.objects o
JOIN sys.columns c ON c.object_id = o.object_id
					AND o.is_ms_shipped <> 1
JOIN INFORMATION_SCHEMA.COLUMNS c1 ON c1.TABLE_NAME = o.name
				   AND c1.COLUMN_NAME = c.name	
WHERE c.name LIKE '%' + @ColumnName + '%'
ORDER BY TableName

I generally find that a like statement is best for this kind of general search but the where clause can always be changed if you only want exact matches. An example output for this query is below: