Query Table Metadata
Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Saint Augustine, FL. Bradley is a former consultant, trainer, and has coauthored 6 SQL Server and Power BI books, most recently Learn Microsoft Fabric. 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. Bradley gives back to the community through speaking at events such as the SQLBits, Fabric Community Conference, PASS Community Data Summit, SQL Saturdays, Code Camps, and user groups across the country including locally at the Jacksonville SQL Server User Group (JSSUG). He is a contributor on SQLServerCentral.com and blogs on his personal site, BradleySchacht.com.
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](https://bradleyschacht.com/search-stored-procedure-text/ "Search 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.