Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Super Quick Table Meta Data Expand / Collapse
Author
Message
Posted Monday, March 23, 2009 2:16 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
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
Posted Tuesday, March 24, 2009 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:33 PM
Points: 7, Visits: 74

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
Posted Tuesday, March 24, 2009 7:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:24 PM
Points: 6, Visits: 49
Hey Jacques

Now it works, thanks for a fine job.

regards Tommy
Post #682301
Posted Tuesday, March 24, 2009 7:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:16 AM
Points: 3, Visits: 50
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
Posted Tuesday, March 24, 2009 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 12:49 PM
Points: 3, Visits: 5
Highlighting the table in SMS and ALT+F1 gives pretty much the same data.
Post #682405
Posted Tuesday, March 24, 2009 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:33 PM
Points: 7, Visits: 74
Hi nathan.j.lalonde, see my previous post, 3 up from yours.
Post #682416
Posted Tuesday, March 24, 2009 8:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:53 AM
Points: 33, Visits: 117
The 2005 version posted worked great! Thanks to all of you.
Post #682455
Posted Tuesday, March 24, 2009 9:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Tuesday, March 24, 2009 10:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 380, Visits: 54
Are going rework it for SQL Server 2005? Thanks.
Post #682562
Posted Tuesday, March 24, 2009 11:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:33 PM
Points: 7, Visits: 74
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 »

Add to briefcase «««1234»»

Permissions Expand / Collapse