Find a Table's Metadata

,

SQL Server stores all of the metadata information that you would ever need to know about a table. However, it is often cumbersome and nominative for programmers to pull out this data using the system tables and special sprocs that are required to find the useful information. I have worked in multiple enterprises where it became my responsibility for keeping track of basic table/field information in a user table despite the fact that this information is readily available in SQL Server's system tables.

Using this procedure, programmers pass in a table name and schema for a user table and receive tuples of fields that contain the data type, length, description, and the lookup table if there is a foreign key constraint. Our programmers now use this sproc to dynamically generate data entry forms based on the table's metadata without having to understand the nuances of how this data is stored in SQL Server.

Our DBAs also use it to gain a quick glimpse into how a table is designed without using the graphical tools of Management Studio or Enterprise Manager.
The procedure could be tailored to output additional information specific to a program/organization.

create procedure dbo.sproc_TABLEINFO
-----------------------------------------------------------------------
--Returns Basic Table Information
--Author : Christopher Crosbie 
-----------------------------------------------------------------------

(
@table varchar(100), --User Table 
@schema varchar(50) --Schema that table belongs to
)
AS
BEGIN 

--Two temp tables for storing our lookup table and field description
declare @temp table 
(
col sql_variant,
description sql_variant
)

declare @templookup table 
(
object_id int,
column_id int,
lookuptable sql_variant
)
--Description of column is stored as an extended properity and 
--requires a system function for reading this infomation
insert into @temp
SELECT objname AS objName,[value] FROM
::fn_listextendedproperty (default, 'schema', @schema, 'table',@table,'column',null)
where fn_listextendedproperty.[name] = 'MS_Description'

--finding a foreign key associated with a field 
--assuming that we are just using a basic lookup table 
--could modify to find additional information

declare @objID int --id of the user table we passed to the sproc

select @objID=object_id
from sys.objects o
inner join sys.schemas s
on s.schema_id = o.schema_id
where o.[name] = @table
and s.[name] = @schema
and o.type = 'U' --User table only 
--could change this to work with views, system tables, etc


insert into @templookup
select col.object_id , col.column_id, tbl.[name] AS LookupTable
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc
on fkc.constraint_object_id = fk.object_id
inner join sys.columns col
on col.object_id = fkc.parent_object_id and fkc.parent_column_id=col.column_id
inner join sys.tables tbl
on tbl.object_id = fkc.referenced_object_id 
where fk.parent_object_id = @objID


select c.[Name] AS Field, d.[Name] As DataType , c.max_length AS Length ,tt.description As FieldDescription, tl.Lookuptable As LookupTable
from sys.tables t
inner join sys.columns c 
on c.object_id = t.object_id
inner join sys.schemas s
on s.schema_id = t.schema_id
inner join sys.types d
on d.system_type_id = c.system_type_id
left outer join @temp tt
on ltrim(rtrim(cast(tt.col as varchar))) = ltrim(rtrim(c.[Name])) --since joining by name I am making sure there is no white space. 
left outer join @templookup tl
on tl.object_id = c.object_id and tl.column_id = c.column_id
where t.[name] = @table
and s.[name] = @schema

END

Rate

5 (2)

Share

Share

Rate

5 (2)