need help with data dictionary query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

need help with data dictionary query

Author

Topic

rb1373
Yak Posting Veteran

93 Posts

Posted - 2006-03-09 : 12:59:20

I need help generating the data for last 4 columns in a data dictionary table.

Below is the table and my query so far.

TIA,
Ray - SS2K

CREATE TABLE [dbo].[DataDictColumn] (
[CatalogServer] [varchar] (128) NULL ,
[CatalogName] [nvarchar] (128) NULL ,
[TableName] [sysname] NULL ,
[TableType] [varchar] (5) NULL ,
[ColumnName] [sysname] NULL ,
[ColumnDefault] [nvarchar] (4000) NULL ,
[IsNullable] [varchar] (3) NOT NULL ,
[DataType] [nvarchar] (128) NULL ,
[Length] [smallint] NULL ,
[NumericPrecision] [tinyint] NULL ,
[Scale] [int] NULL ,
[ColumnDescription] [varchar] (2000) NULL ,
[ColumnRules] [varchar] (2000) NULL ,
[IsPK] [tinyint] NULL DEFAULT (0),
[IsFK] [tinyint] NULL DEFAULT (0)
)
END

select top 5 @@servername, isc.table_catalog, sysobjects.name, case xtype when 'U' then 'Table' when 'V' then 'View' else '' end ,
isc.column_name, column_default, is_nullable, data_type,
character_maximum_length, numeric_precision, numeric_scale
from INFORMATION_SCHEMA.COLUMNS isc
join sysobjects on isc.column_name = sysobjects.name
where type in ('u', 'v')

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-03-09 : 17:23:43

To get the FK/PK, use the views KEY_COLUMN_USAGE and REFERENTIAL_CONSTRAINTS. For the Column Desc you'll probably need to use teh sysproperties table because I don't think descriptions are included in Info Schema views.
What do you mean by ColumnRules?

HTH,

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources