Schema Map

Need a complete map of a database's schema in one result set? This script produces, for every user table column: the schema, table and column names, whether it is an identity or primary key, its full data type (with length/precision), nullability, the foreign keys it participates in (source column mapped to all target columns), and any MS_Description extended property as comments. It builds the FK mapping first in temp tables, then joins everything together.

SET NOCOUNT ON;

if object_id('tempdb..#AllFKs') is not null
begin--if
	drop table #AllFKs
end--if

if object_id('tempdb..#FKs') is not null
begin--if
	drop table #FKs
end--if

select
	object_name(SysFK.parent_object_id) + '.' + SysSourceC.name as [Source],
	object_name(SysFK.referenced_object_id) + '.' + SysTargetC.name as [Target]
into
	#AllFKs
from
	sys.foreign_keys as SysFK
inner join sys.foreign_key_columns as SysFKC
	on SysFKC.constraint_object_id = SysFK.object_id
inner join sys.columns as SysSourceC
	on SysSourceC.object_id = SysFK.parent_object_id
	and SysSourceC.column_id = SysFKC.parent_column_id

inner join sys.columns as SysTargetC
	on SysTargetC.object_id = SysFK.referenced_object_id
	and SysTargetC.column_id = SysFKC.referenced_column_id

SELECT TOP 0 * INTO #FKs FROM #AllFKs

declare SourceFKs cursor fast_forward for
	select distinct
		[Source]
	from
		#AllFKs
open SourceFKs

declare @Source varchar(256)
declare @Target varchar(256)
declare @AllTargets varchar(256)

fetch from SourceFKs into @Source

while(@@FETCH_STATUS = 0)
begin--if

	declare TargetFKs cursor fast_forward for
		select
			[Target]
		from
			#AllFKs
		where
			[Source] = @Source
	open TargetFKs

	fetch from TargetFKs into @Target

	SET @AllTargets = ''

	while(@@FETCH_STATUS = 0)
	begin--if
		--select @Source, @Target

		SET @AllTargets = IsNull(@AllTargets, '') + @Target

		fetch from TargetFKs into @Target

		IF(@@FETCH_STATUS = 0)
		begin--if
			SET @AllTargets = IsNull(@AllTargets, '') + ', '
		end--if
	end--if

	insert into #FKs
	(
		[Source],
		[Target]
	)
	select
		@Source,
		@AllTargets

	close TargetFKs deallocate TargetFKs

	fetch from SourceFKs into @Source
end--if

close SourceFKs deallocate SourceFKs

select
	schema_name(SysO.schema_id) as [Schema],
	SysO.Name as [Table],
	SysC.Name as [Column],
	CASE WHEN SysC.is_identity = 1 THEN 'Y' ELSE '' END AS [Is Identity?],

	IsNull((
		select TOP 1
			'Y'
		from
			sys.indexes as SysI
		inner join sys.index_columns as SysIC
			on SysIC.object_id = SysI.object_id
		where
			SysI.is_primary_key = 1
			and SysI.object_id = SysO.object_id
			and SysIC.column_id = SysC.column_id
	), '') as [Is Primary Key?],

	SysT.name +
		CASE
			WHEN SysT.name in ('varchar', 'nvarchar', 'char', 'nchar', 'binary', 'varbinary')
				THEN ' (' + CASE WHEN Cast(SysC.max_length as varchar) = '-1' THEN 'MAX' ELSE Cast(SysC.max_length as varchar) END + ')'
			WHEN SysT.name in ('decimal')
				THEN ' (' + Cast(SysC.[precision] as varchar) + ',' + Cast(SysC.[scale] as varchar) + ')'
			ELSE
				''
		END as [Type],

	CASE WHEN SysC.is_nullable = 1 THEN 'Y' ELSE '' END AS [Is Nullable?],

	IsNull((select [Target] from #FKs where [Source] = SysO.Name + '.' + SysC.Name), '') as [Foreign Key],
	IsNull('"' + Replace((
		select
			Convert(nVarChar(max), SysEP.value) [Description]
		from
			sys.extended_properties as SysEP
		where
			SysEP.major_id = SysO.object_id
			and SysEP.minor_id = SysC.column_id
			and SysEP.name = 'MS_Description'
	), '"', '''') + '"', '') as Comments
from
	sys.objects as SysO
inner join sys.columns as SysC
	on SysC.object_id = SysO.object_id
inner join sys.types as SysT
	on SysT.user_type_id = SysC.user_type_id
where
	SysO.type = 'U'
	and SysO.is_ms_shipped = 0
	and SysO.name not in ('sysdiagrams')

drop table #AllFKs
drop table #FKs