queries

find version

select @@version

find columns

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

find tables with a column[^1]

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

find all keys

SELECT tab.[name] AS table_name,
         substring(column_names,
         1,
         len(column_names)-1) AS [columns],
         pk.[name] AS pk_name
FROM sys.tables tab
INNER JOIN sys.indexes pk
    ON tab.object_id = pk.object_id
        AND pk.is_primary_key = 1 cross apply 
    (SELECT col.[name] + ', '
    FROM sys.index_columns ic
    INNER JOIN sys.columns col
        ON ic.object_id = col.object_id
            AND ic.column_id = col.column_id
    WHERE ic.object_id = tab.object_id
            AND ic.index_id = pk.index_id
    ORDER BY  col.column_id for xml path ('') ) D (column_names)
ORDER BY  table_name

find relationships

-- change changeme
SELECT t.name AS TableWithForeignKey,
         fk.constraint_column_id AS FK_columns ,
         c.name AS ForeignKeyColumn
FROM sys.foreign_key_columns AS fk
INNER JOIN sys.tables AS t
    ON fk.parent_object_id = t.object_id
INNER JOIN sys.columns AS c
    ON fk.parent_object_id = c.object_id
        AND fk.parent_column_id = c.column_id
WHERE fk.referenced_object_id = 
    (SELECT object_id
    FROM sys.tables
    WHERE name = 'changeme')
ORDER BY  TableWithForeignKey, ForeignKeyColumn 

except

SELECT FAKETABLE.YOURCOLUMN 
FROM (VALUES('__'),('__'),('__'),('__')) AS FAKETABLE(YOURCOLUMN) 
EXCEPT 
SELECT YOURCOLUMN 
FROM YOURTABLE 
WHERE YOURCOLUMN = '___' 

Comments