Query Table Metadata

There will inevitably come a day when you want to take a look at the metadata of the tables inside a database. This can be accomplished one of many ways, the quickest and easiest is to expand the columns node inside the Object Explorer in SQL Server Management Studio (SSMS). What if you want to look at your entire database though? Running a query against INFORMATION_SCHEMA.COLUMNS is a great way to do this. This will get you much more in depth information about your tables with minimal work.

I created the script below that will display some hopefully useful information for you. It combines the data types into one field rather than having to look through all the INFORMATION_SCHEMA.COLUMNS fields and will allow you to see if the field takes NULLs, is computed or is an identity. There are plenty of other things that could be done with this script, but it is a good table summary with a couple of filters in the WHERE clause for limiting to just a particular schema or table.
I hope to create a script library on the site sometime in the near future so that you can come here and download little snippets of code like this from one central location rather than having to parse through blogs if you want something in particular like table metadata or to search through stored procedure text.
It’s also pretty easy to toss this into a view or a CTE to do things like what I needed it for, finding all the identity columns. If it is in a CTE (Not available in all versions of SQL) then you don’t have to put the entire COLUMNPROPERTY(OBJECT_ID(Ta….blah blah in the WHERE clause, you can just say where IsIdentity = 1. Much nicer. I had a case statement around each of those column properties to make them read yes or no instead of 1 or 0, but it was too hard to read. The 1 or 0 is much easier on your eyes and easy to identity which column has the property.
/*Show a database's table metadata*/
SELECT
Table_Schema AS TableSchema,
Table_Name AS TableName,
Column_Name AS ColumnName,
Data_Type AS DataType,
CASE
WHEN DATA_TYPE IN ('bigint', 'int', 'smallint', 'tinyint') THEN CONVERT(VARCHAR,NUMERIC_PRECISION)
WHEN DATA_TYPE IN ('binary','char','hierarchyid','nchar','ntext','nvarchar','text','varbinary','varchar') THEN CASE WHEN CHARACTER_MAXIMUM_LENGTH = '-1' THEN 'MAX' ELSE CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH) END
WHEN DATA_TYPE IN ('decimal','money','numeric','smallmoney') THEN CONVERT(VARCHAR, NUMERIC_PRECISION) + ',' + CONVERT(VARCHAR, Numeric_Scale)
ELSE NULL END AS Length,
ORDINAL_POSITION AS ColumnPosition,
COLUMNPROPERTY (OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'AllowsNull') AS IsNullable,
COLUMNPROPERTY (OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
COLUMNPROPERTY (OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsComputed') AS IsComputed,
COLUMN_DEFAULT AS DefaultValue
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA LIKE '%' AND
TABLE_NAME LIKE '%'
ORDER BY TableSchema, TableName, ORDINAL_POSITION

About the author

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

2 comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • It is actually hard to find advised people on that matter, nevertheless, you be understood as you no doubt know exactly what you are speaking about! Thank you

  • It is actually hard to find advised people on that matter, nevertheless, you be understood as you no doubt know exactly what you are speaking about! Thank you

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me