Important SQL Server queries to find database metadata

Find a column in entire database in all tables.

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 '%test%'
ORDER BY    TableName, ColumnName;

 

Find all tables with regular expression in entire database

select * from sys.tables where name like '%audit%'