Skip to content

Searching a Database Schema

How can you find your way in a Microsoft SQL Server database you didn’t create? Try this SQL script.

This script will let you search by table name, column name, column datatype, or the field a foreign key references.

This last option is very helpful. Say you have a column

EmployeeID

and you want to know all of the tables and fields that reference this column. You would assign:

@source = 'EmployeeID'

Enjoy!

declare @tablename as nvarchar(128)
declare @columnname as nvarchar(128)
declare @typename as nvarchar(128)
declare @source as nvarchar(128)

set @tablename  = NULL
set @columnname = NULL
set @typename   = NULL
set @source     = NULL

select distinct
[tables].name 'Table',
[columns].name 'Column',
type_name([columns].[xtype]) 'Type',
[columns].[length] 'Length',
[columns].[xprec] 'Precision',
[columns].[xscale] 'Scale',
cast([columns].[isnullable] as bit) 'Nullable',
cast((case when [primary_keys].[column_name] is not null then 1 else 0 end) as bit) 'Primary Key',
cast((case when [unique_columns].[column_name] is not null then 1 else 0 end) as bit) 'Unique',
(case when [comments].[text] is not null then [comments].[text] else '' end) 'Default',
(case when [foreign_keys].[pk_column] is not null then [foreign_keys].[pk_table] + '.' + [foreign_keys].[pk_column] else '' end) 'Source',
[columns].colorder 'Ordinal',
[table_sizes].[RowCounts] 'Rows'

from

--Tables and columns
sysobjects [tables]
inner join syscolumns [columns] on [columns].id = [tables].id

--Primary keys
left outer join (
SELECT
	tc.table_name,
	ccu.column_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
	ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE tc.CONSTRAINT_TYPE = 'Primary Key'
) [primary_keys] on [tables].[name] = [primary_keys].[table_name] and [columns].[name] = [primary_keys].[column_name]

--Unique constraints
left outer join (
SELECT
	tc.table_name,
	ccu.column_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
	ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE tc.CONSTRAINT_TYPE = 'Unique'
) [unique_columns] on [tables].[name] = [unique_columns].[table_name] and [columns].[name] = [unique_columns].[column_name]

--Foreign keys
left outer join (
SELECT
o.name 'pk_table',
col_name(rkeyid, rkey) as 'pk_column',
col_name(fkeyid, fkey) as 'fk_column',
o2.name 'fk_table'
FROM sysobjects o
INNER JOIN sysforeignkeys fk on o.id = fk.rkeyid
INNER JOIN sysobjects o2 on fk.fkeyid = o2.id
) [foreign_keys] on [tables].[name] = [foreign_keys].[fk_table] and [columns].[name] = [foreign_keys].[fk_column]
left outer join syscomments [comments] on [columns].[cdefault] = [comments].[id]

--Table sizes
left outer join (
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
	t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
) [table_sizes] on [tables].[name] = [table_sizes].[TableName]

where [tables].[type] = 'U'
AND (@tablename IS NULL OR [tables].[name] LIKE @tablename)
AND (@columnname IS NULL OR [columns].[name] LIKE @columnname)
AND (@typename IS NULL OR type_name([columns].[xtype]) LIKE @typename)
AND (@source IS NULL OR [foreign_keys].[pk_table] + '.' + [foreign_keys].[pk_column] LIKE '%' + @source + '%')
order by [table_sizes].RowCounts desc, [tables].[name], [columns].[colorder]
Published inPosts