T-SQL Tuesday – Tools I use

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:

    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
T-SQL Tuesday - Relationships
Relationships returned from above query

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):

T-SQL Tuesday - Columns
Tables with columns and descriptions (sorry for the danish wording)

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

Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.

Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at help@brianbonk.dk. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

Leave a Reply

Your email address will not be published. Required fields are marked *