This is my first contribution to the T-SQL Tuesday. So wish me luck.
The T-SQL Tuesday is a monthly blog party that happens on the second Tuesday of each month. The very first one was started over a decade ago by Adam Machanic (blog|twitter), and currently is being maintained by Steve Jones (blog|twitter). More about the concept on its website.
This edition is hosted by Mikey Bronowski and covers all the awesome and outstanding tools that we all use in our daily work around the SQL server.
Dynamic Management Views for documentation
I, like most of us, are not “in the zone” when writing documentation for the completed work.
So I often tend to be a little lazy and try to figure out eazier ways to get the documentation done.
For instance – all the tables in a solution can be extracted via the Dynamic Management Views:
SELECT sys.objects.name AS TableName, ep.value AS Description
FROM sys.objects
CROSS APPLY fn_listextendedproperty(default,
'SCHEMA', schema_name(schema_id),
'TABLE', name, null, null) ep
WHERE sys.objects.name NOT IN ('sysdiagrams')
ORDER BY sys.objects.name
Above query gives me all the information I need for the documentation around tables in my solution. I often write desciptions in the META tags of the tables I’ve created, in order to help developers see some sort of usage and description directly in the database.
Relationships between tables – all the Foreign Keys’ relationship to Primary Keys between tables, can be extracted with below code snippet:
SELECT
fk.name 'FK Name',
tp.name 'Parent table',
tr.name 'Refrenced table',
fk.is_disabled as 'Non check constraint'
FROM sys.foreign_keys fk
INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
where fk.schema_id = 7
ORDER BY tp.name, cp.column_id
Columns and their attributes (with description) can be extracted with below code snippet:
SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, ep.value AS Description
FROM sys.objects
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
CROSS APPLY fn_listextendedproperty(default,
'SCHEMA', schema_name(schema_id),
'TABLE', sys.objects.name, 'COLUMN', sys.columns.name) ep
ORDER BY sys.objects.name, sys.columns.column_id
The results are (sorry for the danish text in the Description field):
Summary – T-SQL Tuesday
This is just some of the tools I use for my daily work with the SQL Server. There are many others, and you can find all the awesome suggestions and blogpost about this here:
T-SQL Tuesday #135: The outstanding tools of the trade that make your job awesome
English 
Danish