I got it to work on Sql2005 with minor changes
Here's the code
if exists (select * from sys.objects where object_id = OBJECT_ID(N'[dbo].[TableInfo]') and type in (N'P', N'PC'))
drop procedure [dbo].[TableInfo]
go
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[TableInfo]
@TableNam varchar (700),-- Can include schema/owner.
@FieldNameFilter varchar (700) = null, -- Compared with the like operator.
@Extended bit = 0 -- For showing extra tableInfo such as triggers
as
--*****************************************
-- Author:Jacques Bosch
-- Last Modified:25 Feb 2009
--*****************************************
set concat_null_yields_null off
declare @DBName varchar (20)
Set @DBName = PARSENAME (@TableNam, 3)--Stuart changed
declare @TableSchema varchar (20)
Set @TableSchema= PARSENAME (@TableNam, 2)--Stuart changed
Declare @TableName varchar (700)
set @TableName = PARSENAME (@TableNam, 1)--Stuart changed
if @DBName is not null
and @DBName != DB_NAME()
begin
print 'Cannot run this on DB ''' + @DBName + '''. Must be run on current DB.'
return;
end
-- Set up some values for displaying the results.
declare @Y varchar (10)
Set @Y = ' y' -- Spacing is for nicer look.
declare @Empty varchar (1)
Set @Empty = ''; --Stuart changed. Added the ';'
-- First get all the existing constraint tableInfo that is needed for the table.
-- We are querying into a common table expression.
with tableConstraints
as
(
select distinct
tbl.TABLE_NAME,
kcu.TABLE_SCHEMA,
col.COLUMN_NAME,
tc.CONSTRAINT_NAME,
tc.CONSTRAINT_TYPE,
tc.TABLE_SCHEMA + '.' + OBJECT_NAME(sfk.RKEYID) as FK_Table,
COL_NAME(RKEYID, RKEY) as FK_Field,
OBJECTPROPERTY(sfk.CONSTID, 'CNSTISDISABLED') as FK_Disabled
from
Information_Schema.Tables tbl
inner join
Information_Schema.Columns col
on col.TABLE_NAME = tbl.TABLE_NAME
inner join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
on kcu.TABLE_NAME = col.TABLE_NAME
and kcu.COLUMN_NAME = col.COLUMN_NAME
inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
on tc.TABLE_NAME = kcu.TABLE_NAME
and tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
left outer join
SYSFOREIGNKEYS sfk
on OBJECT_NAME(sfk.CONSTID) = kcu.CONSTRAINT_NAME
and OBJECT_NAME(sfk.FKEYID) = kcu.TABLE_NAME
and COL_NAME(FKEYID, FKEY) = kcu.COLUMN_NAME
where
kcu.TABLE_NAME = @TableName
and
(
@TableSchema is null
or kcu.TABLE_SCHEMA = @TableSchema
)
),
otherConstraint
as
(
-- List the first of other tableConstraints, such as a unique constraint.
select top 1
cons.TABLE_SCHEMA,
cons.TABLE_NAME,
cons.COLUMN_NAME,
cons.CONSTRAINT_TYPE
from
INFORMATION_SCHEMA.Columns c
inner join
tableConstraints cons
on cons.TABLE_SCHEMA = c.TABLE_SCHEMA
and cons.TABLE_NAME = c.TABLE_NAME
and cons.COLUMN_NAME = c.COLUMN_NAME
and cons.CONSTRAINT_TYPE not in ('PRIMARY KEY', 'FOREIGN KEY')
),
tableIndexes
as
(
select
OBJECT_SCHEMA_NAME (sc.object_id) as TABLE_SCHEMA,
OBJECT_NAME (sc.object_id) as TABLE_NAME,
sc.name as COLIMN_NAME,
-- Because we can have more than one index per column, this will cause multiple
-- rows per column in our results set. Hence, we aggregate to prevent this, showing
-- only one index per column.
MIN (i.type_desc) as type_desc, -- Prefers clusterred over nonclusterred if both exist.
-- We cast to tinyint because MIN and MAX don't work with bit.
MAX (CAST (i.is_unique as tinyint)) as is_unique,-- Prefers unique indexes over non-unique.
MIN (CAST (i.is_disabled as tinyint)) as is_disabled, -- Prefers enabled indexes over disabled.
MAX (CAST (ic.is_descending_key as tinyint)) as is_descending_key -- Prefers descending indexes over ascending.
from
sys.columns sc
inner join
sys.indexes i
on i.object_id = sc.object_id
inner join
sys.index_columns ic
on ic.index_id = i.index_id
and ic.object_id = sc.object_id
and ic.column_id = sc.column_id
where
object_name (sc.object_id) = @TableName
and
(
@TableSchema is null
or object_schema_name (sc.object_id) = @TableSchema
)
group by
OBJECT_SCHEMA_NAME (sc.object_id), -- TABLE_SCHEMA
OBJECT_NAME (sc.object_id), -- TABLE_NAME
sc.name -- COLIMN_NAME
),
tableInfo
as
(
-- Gather the info we want to display.
select distinct
c.TABLE_SCHEMA as [Schema],
-- Show Primary Key
case when pk.TABLE_NAME IS NOT NULL
then
'pk'
else
@Empty
end as PK,
-- Show FOREIGN KEY
case when fk.TABLE_NAME IS NOT NULL
then
'fk'
else
@Empty
end
+ -- Show when the foreign key is disabled.
case
when ISNULL (fk.FK_Disabled, 0) = 1 then
' (disabled)'
else
@Empty
end
as FK,
-- Show index tableInfo, such as clustered / nonclustered, if unique, if descending, if disabled, etc.
case
when ix.COLIMN_NAME is not null then
case
when ix.type_desc = 'CLUSTERED' then 'c'
when ix.type_desc = 'NONCLUSTERED' then 'nc'
else ix.type_desc
end
+ case
when ix.is_unique = 1 then ', unique'
else @Empty
end
+ case
when ix.is_descending_key = 1 then ', desc'
else @Empty
end
+ case
when ix.is_disabled = 1 then ' (disabled)'
else @Empty
end
else
@Empty
end
as IX,
-- Show the first of any other CONSTRAINTS
ISNULL (oc.CONSTRAINT_TYPE, @Empty) as Cons,
c.COLUMN_NAME as ColumnName,
-- Show the data type.
case
when c.DATA_TYPE like '%int' then
c.DATA_TYPE
when c.DATA_TYPE = 'bit' then
c.DATA_TYPE
when c.DATA_TYPE like '%datetime' then
c.DATA_TYPE -- + ' (' + CAST (c.DATETIME_PRECISION as VARCHAR) + ')'
when c.DATA_TYPE like '%char%'then
c.DATA_TYPE + ' (' + CAST (c.CHARACTER_MAXIMUM_LENGTH as VARCHAR) + ')'
when c.NUMERIC_PRECISION IS NOT NULL and c.NUMERIC_SCALE IS NOT NULL then
c.DATA_TYPE + ' (' + CAST (c.NUMERIC_PRECISION as VARCHAR) + ',' + CAST (c.NUMERIC_SCALE as VARCHAR) + ')'
else
c.DATA_TYPE
end
+ -- After datatype, also show if identity.
case
when syscol.[status] = 128 then -- 128 = Identity
' (identity)'
else
@Empty
end
as DataType,
case
when c.IS_NULLABLE = 'yes' then
@Y
else
@Empty
end as Nullable,
case
when COLUMNPROPERTY(syscol.id, syscol.name, 'IsComputed') = 1 then
@Y
else
@Empty
end as Computed,
-- Default value of column.
c.COLUMN_DEFAULT as [Default],
-- Show foreign key table if there is one
case
when fk.TABLE_NAME IS NOT NULL then
fk.FK_Table
else
@Empty
end as [FK Table],
-- Show the column in the foreign key table to which the key relates.
case
when fk.TABLE_NAME IS NOT NULL then
fk.FK_Field
else
@Empty
end as [FK Field],
-- Show the name of the constraint if there is one.
case
when fk.CONSTRAINT_NAME IS NOT NULL then
fk.CONSTRAINT_NAME
else
@Empty
end as [Constraint Name],
-- For showing the results in the correct order.
C.ORDINAL_POSITION
from
Information_Schema.Tables t
inner join
INFORMATION_SCHEMA.Columns c
on c.TABLE_SCHEMA = t.TABLE_SCHEMA
and c.TABLE_NAME = t.TABLE_NAME
inner join
syscolumns syscol
on OBJECT_NAME(syscol.id) = t.TABLE_NAME
and syscol.Name = c.COLUMN_NAME
-- Including schema in join incurs big performance hit, and shouldn't be necessary (mostly).
-- and OBJECT_SCHEMA_NAME (syscol.id) = c.TABLE_SCHEMA
left outer join
tableConstraints pk
on pk.TABLE_SCHEMA = t.TABLE_SCHEMA
and pk.TABLE_NAME = t.TABLE_NAME
and pk.COLUMN_NAME = c.COLUMN_NAME
and pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
left outer join
tableConstraints fk
on fk.TABLE_SCHEMA = t.TABLE_SCHEMA
and fk.TABLE_NAME = t.TABLE_NAME
and fk.COLUMN_NAME = c.COLUMN_NAME
and fk.CONSTRAINT_TYPE = 'FOREIGN KEY'
left outer join
otherConstraint oc
on oc.TABLE_SCHEMA = t.TABLE_SCHEMA
and oc.TABLE_NAME = t.TABLE_NAME
and oc.COLUMN_NAME = c.COLUMN_NAME
left outer join
tableIndexes ix
on ix.TABLE_SCHEMA = t.TABLE_SCHEMA
and ix.TABLE_NAME = t.TABLE_NAME
and ix.COLIMN_NAME = c.COLUMN_NAME
where
t.TABLE_NAME = @TableName
and
(
@TableSchema IS NULL
or t.TABLE_SCHEMA = @TableSchema
)
)
-- Return the results
select
[Schema],
PK,
FK,
IX,
Cons,
ColumnName,
DataType,
Nullable,
Computed,
[Default],
[FK Table],
[FK Field],
[Constraint Name]
from tableInfo
where
(
@TableSchema IS NULL
or [Schema] = @TableSchema
)
and
(
@FieldNameFilter IS NULL
or ColumnName LIKE @FieldNameFilter
)
order by
[Schema],
ORDINAL_POSITION
-- Also return the list of triggers on this table if there are any, and if extended tableInfo is on.
if @Extended = 1
and exists (select * from sys.triggers where OBJECT_NAME (parent_id) = @TableName)
begin
select
t.name as [Trigger Name],
case when t.is_ms_shipped = 1 then @Y else @Empty end as [MS Shipped],
case when t.is_disabled = 1 then @Y else @Empty end as [Disabled],
case when t.is_instead_of_trigger = 1 then @Y else @Empty end as [Instead Of],
case when t.is_not_for_replication = 1 then @Y else @Empty end as [Not for Replication],
t.modify_date as Modified,
t.create_date as Created
from
sys.triggers t
where
OBJECT_NAME (t.parent_id) = @TableName
end