I answered a forum question the other day about how to list all the columns in a SQL Server database. That's straightforward enough, but they also asked for all the extended properties for the column.
In case you need to do this, here's some code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT s.[name] AS SchemaName, t.[name] AS TableName, c.[name] AS ColumnName, c.is_nullable AS IsNullable, typ.[name] AS DataTypeName, c.max_length AS MaximumLength, c.[precision] AS [Precision], c.scale AS Scale, ep.ExtendedPropertyName, ep.ExtendedPropertyValue FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.object_id = c.object_id INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id INNER JOIN sys.types AS typ ON typ.system_type_id = c.system_type_id AND typ.user_type_id = c.user_type_id OUTER APPLY ( SELECT ep.[name] AS ExtendedPropertyName, ep.[value] AS ExtendedPropertyValue FROM sys.extended_properties AS ep WHERE ep.major_id = c.object_id AND ep.minor_id = c.column_id ) AS ep WHERE t.is_ms_shipped = 0 AND t.[name] <> N'sysdiagrams' ORDER BY SchemaName, TableName, ColumnName, ExtendedPropertyName; |
How it works
I start with the sys.columns view and join it to sys.tables and sys.schemas, to get the schema and table name. The other reason is to make sure it's not a Microsoft-supplied table. I also wanted to exclude the sysdiagrams table that is created when you first create a database diagram using SQL Server Management Studio. (Curiously, that one's not flagged as a Microsoft-supplied table).
The next join was to sys.types to get details of the data type. Notice that the join is on both system_type_id and user_type_id. You need to do that to allow for aliased data types. If you want the underlying base-type, change the ON clause to this:
|
1 2 |
ON typ.system_type_id = c.system_type_id AND typ.user_type_id = c.system_type_id |
While the first query would return names like sysname, the alternative ON clause would return same column as the underlying type like nvarchar(128).
Finally, I'm outer applying a query that finds the extended properties for a column. The sys.extended_properties view provides that. It can work with many different types of objects, so you need to closely define what you're looking for. The major_id is the object (i.e. the table) and the minor_id is the column_id. I've used an OUTER APPLY to make sure the column comes back, even if it doesn't have any extended properties.
If you only want columns with extended properties, you could change the OUTER APPLY to a CROSS APPLY instead.
Hi Greg
Thanks for the script with detailed explanation. Recently, for one of my clients I had to do something similar on a set of Azure SQL databases. Since Azure SQL db do not allow cross database joins (unless you use elastic query) , I created a python script that gets the list of all databases on the Azure SQL server, iterates through each database and in each iteration makes a connection to the database, run your script and close the connection. Finally union all dataframes together. It worked perfectly fine. Thanks for the great blog.