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%'
Hope this helps. Good day!