Find a Table's Metadata

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