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