When I encounter a new database, I want to get familiar with it. Navigating around in Object Explorer can only go so far. What if you’re looking for a specific table? How about a specific column? Let’s break down each search with some code.
Search for tables* within a database:
*Code returns both tables and views.
SELECT
T.TABLE_CATALOG AS DatabaseName,
T.TABLE_SCHEMA AS TableSchema,
T.TABLE_NAME AS TableName,
T.TABLE_TYPE AS TableType
FROM
INFORMATION_SCHEMA.TABLES AS T
WHERE
T.TABLE_NAME like '%Post%' --Table Name to search forOutput:
Search for columns within a database:
SELECT
T.TABLE_CATALOG AS DatabaseName,
T.TABLE_SCHEMA AS TableSchema,
T.TABLE_NAME AS TableName,
T.TABLE_TYPE AS TableType,
C.ORDINAL_POSITION AS ColumnID,
C.COLUMN_NAME AS ColumnName,
C.DATA_TYPE,
C.COLUMN_DEFAULT AS ColumnDefaultValue
FROM
INFORMATION_SCHEMA.TABLES AS T
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C ON T.TABLE_NAME = C.TABLE_NAME
WHERE
C.COLUMN_NAME LIKE '%User%' --Column Name to search forOutput:
There are more columns in INFORMATION_SCHEMA.COLUMNS, so be sure to check those out.