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.
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](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. /*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