Super Quick Table Meta Data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jacques Bosch » Super Quick Table Meta Data

38 posts, Page 3 of 4

«««1 2 3 4 »»

Super Quick Table Meta Data

Rate Topic

Display Mode

Topic Options

Author

Message

Bruce W Cassidy

Posted Monday, March 23, 2009 2:16 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

Here's a modified form that will work with SQL Server 2005.

Note that I have not reviewed the code other than to change the issue of assignment during variable declaration (a SQL Server 2008 feature.)

Post Attachments

TableInfo.sql.txt (11 views, 9.73 KB)

Post #681834

Jacques-897195

Posted Tuesday, March 24, 2009 6:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:46 PM
Points: 7, Visits: 69

Thank you, Bruce W Cassidy, for posting the SQL 2K5 version, although GSquared had already uploaded one in an earlier post.
Tommy Balle, and randal.schmidt, did you come right with the 2K5 versions provided by the guys above? I'm sorry that I didn't test it for compatibility with SQL Server 2005, but in my own defense, I did mention this right at the end of the article.

Rich Holt, can you point out the character case problems that you mentioned?

hardtarget_x wrote:
>You know a feature that would be neat to add, if you left the table empty or put in a %, and then typed in a column name... Then return all
>of the tables that hold that specific column name.
Yes I like that. Then you could get a quick overview of the columns you are getting back from multiple tables and compare their data types etc.

Concerning the sp_help proc, yes I should have probably mentioned it. It definitely returns more info than TableInfo does. However, the TableInfo proc, when not using the extended flag, returns a compact yet reasonably comprehensive single resultset. It very clearly shows up which columns have indexes and foreign keys, and to which tables and fields those link to. When using it, most info relevant to a column is right there in the single row for that column, and you don't have to scan through the multiple resultsets as you would have to when using sp_help. Mix and use them as you want, but personally I very rarely have to revert back to sp_help to get what I'm looking for.

As Tony Webster said, if we don't like, or have a use for what comes out of the box, we can write something that better suits our needs / method of working. We can change the box :)

Post #682238

Tommy Balle

Posted Tuesday, March 24, 2009 7:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 1:10 AM
Points: 6, Visits: 43

Hey Jacques

Now it works, thanks for a fine job.

regards Tommy

Post #682301

Stuarts-821176

Posted Tuesday, March 24, 2009 7:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 1:26 PM
Points: 3, Visits: 48

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

Post #682302

nathan.j.lalonde

Posted Tuesday, March 24, 2009 8:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 03, 2010 12:49 PM
Points: 3, Visits: 5

Highlighting the table in SMS and ALT+F1 gives pretty much the same data.

Post #682405

Jacques-897195

Posted Tuesday, March 24, 2009 8:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:46 PM
Points: 7, Visits: 69

Hi nathan.j.lalonde, see my previous post, 3 up from yours.

Post #682416

randal.schmidt

Posted Tuesday, March 24, 2009 8:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 9:46 AM
Points: 33, Visits: 116

The 2005 version posted worked great! Thanks to all of you.

Post #682455

epriddy

Posted Tuesday, March 24, 2009 9:42 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, October 22, 2012 4:46 PM
Points: 77, Visits: 110

We currently have data dictionaries for much of our data warehouse, but as we build new tables (and corresponding data dictionaries) this will be helpful. Thanks for the procedure!

My minor modifications for 2005 were to set values (instead of default):

declare @DBName varchar (20)
Set @DBName = PARSENAME (@TableName, 3)
declare @TableSchema varchar (20)
Set @TableSchema = PARSENAME (@TableName, 2)
set @TableName = PARSENAME (@TableName, 1)
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 = '';

I've sent it to the rest of my team and will let you know what they think of it :)

Post #682503

Bridget Elise Nelson

Posted Tuesday, March 24, 2009 10:32 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 5:09 AM
Points: 380, Visits: 41

Are going rework it for SQL Server 2005? Thanks.

Post #682562

Jacques-897195

Posted Tuesday, March 24, 2009 11:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:46 PM
Points: 7, Visits: 69

Thanx, epriddy, glad to hear it.

Bridget, that's been dome multiple times before. You can found SQL 2005 versions in other posts.

Seems like reading previous posts before posting is not always a common habit. ;)

Don't know if I can modify my article to include the 2K5 version there. I'll find out.

Post #683048

« Prev Topic | Next Topic »

38 posts, Page 3 of 4

«««1 2 3 4 »»

Permissions