SQL: Column metadata query

A friend of mine asked a question on how to retrieve SQL column metadata, hence wrote this query with help from stack overflow.

I had to make some adaptations; basically used a nested query to create temporary result set in order to apply a filter on a column alias.

In order to use this query please remember to change value for the Where clause on column alias TableName. For e.g. ‘%MyTableName%’ to filter any TableName containing string ‘MyTableName’. For exact results please remove %% from the where clause. For e.g. ‘MyTableName’.

You can add more columns to this output by choosing columns from output of following query: select * from sys.columns. Then add the column to the nested query along with an appropriate column alias.

Select * from 
(Select 
object_NAME(Columns.object_id) As TableName, 
Columns.name As 'ColumnName', 
Types.name As 'Type', 
Columns.max_length As 'Length/Size'
from sys.columns Columns INNER JOIN sys.types Types  ON Types.system_type_id = Columns.system_type_id) 
AllColumnMetaData
Where TableName like '%sysrscols%'

Column metadata query output screenshot.

Hope this helps. Good day!