Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bradley Schacht

Bradley is a consultant at Pragmatic Works in Jacksonville, FL. He was an author on the book SharePoint 2010 Business Intelligence 24-Hour Trainer and tech edited the SQL 2011 Bible. His experience on the Microsoft BI platform includes DTS, SSIS, SSRS, SSAS and migrations and conversions. He has helped numerous companies in successfully developing and implementing new business intelligence solutions into their organizations. Bradley also participates as a speaker in community events like SQL Saturday, Code Camp, SQL Lunch and SQL Server User Groups. He is a contributor on sites such as BIDN.com and SQL Server Central as well as an active member of the Jacksonville SQL Server User Group (JSSUG).

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.