March 23, 2009 at 9:35 am
ola
thought i saw that code somewhere, was thinking of until ... your name dawned on me 😀
March 23, 2009 at 10:17 am
By default, we use a BINARY collation on my team. The TableInfo code has a few character case problems, which were easy to find and fix. Same goes for the assignment of default values to local variables; easy enough to fix in the script.
Don't make the assumption that you can prefix the proc name with "sp_" , create it in master, and have it work in any User database. It won't necessarily generate any errors, but it also won't generate any output.
Thanks for the example, it presents some food for thought. 🙂
March 23, 2009 at 10:40 am
Hi Rich,
This seems to scare some people but it works very nicely in my experience:
By setting stored procedures as being a "system object", you can create them in the master database, prefix them with "sp_", and have them run in any database "natively".
The proc for doing this is "sp_ms_marksystemobject" - it works in 2000 and 2005, I have not tested in 2008.
Hope this helps!
Tao
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
March 23, 2009 at 12:18 pm
But surprisingly, not triggers.
March 23, 2009 at 12:20 pm
Just change the lines that read:
declare @DBName varchar (20) = PARSENAME (@TableName, 3)
to
declare @DBName varchar (20)
set @DBName = PARSENAME (@TableName, 3)
SQLServer 2005 just requires 2 rows instead of 1 row.
March 23, 2009 at 2:16 pm
[font="Verdana"]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.)
[/font]
March 24, 2009 at 6:27 am
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 🙂
March 24, 2009 at 7:26 am
Hey Jacques
Now it works, thanks for a fine job.
regards Tommy
March 24, 2009 at 7:26 am
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
March 24, 2009 at 8:29 am
Highlighting the table in SMS and ALT+F1 gives pretty much the same data.
March 24, 2009 at 8:33 am
Hi nathan.j.lalonde, see my previous post, 3 up from yours.
March 24, 2009 at 8:58 am
The 2005 version posted worked great! Thanks to all of you.
March 24, 2009 at 9:42 am
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 🙂
March 24, 2009 at 10:32 am
Are going rework it for SQL Server 2005? Thanks.
March 24, 2009 at 11:36 pm
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.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply