Thursday, 13 June 2013

MSSQL - Query Templates - usage of system tables

Sometimes you have to find all the tables (their names), which have the field with the specified name.
The easiest way to do this is through queries to the system tables.
Because this task occurs frequently, I have created T-SQL template:
-- Find Tables by Column-Name
USE <DataBaseName, sysname, tempdb>;

SELECT
    [SCHEMA] = schemas.name,
    [TABLE]  = tables.name,
    [COLUMN] = columns.name
FROM
    sys.schemas
    INNER JOIN sys.tables ON
        schemas.schema_id = tables.schema_id
    INNER JOIN sys.columns ON
        tables.object_id  = columns.object_id
WHERE
    schemas.name IN ('<schema_name, sysname, dbo>')
    AND
    tables.name  LIKE '<TableNamePattern, sysname, %>'
    AND
    columns.name LIKE '<ColumnNamePattern, sysname, %_ID>'
ORDER BY
    [SCHEMA],
    [TABLE],
    [COLUMN];
Save this template to your templates folder with for example following name:
"SysTables - Sch-Tb-Clm". After replacing of placeholders you will see something like this:
-- Find Dimension-Tables with Code-Columns
USE AdventureWorksDW2008R2;

SELECT
    [SCHEMA] = schemas.name,
    [TABLE]  = tables.name,
    [COLUMN] = columns.name
FROM
    sys.schemas
    INNER JOIN sys.tables ON
        schemas.schema_id = tables.schema_id
    INNER JOIN sys.columns ON
        tables.object_id  = columns.object_id
WHERE
    schemas.name IN ('dbo')
    AND
    tables.name  LIKE 'Dim%'
    AND
    columns.name LIKE '%Code'
ORDER BY
    [SCHEMA],
    [TABLE],
    [COLUMN];

The result of execution will be something like this: