INFORMATION_SCHEMA - SQL Server Forums

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

General SQL Server Forums

Database Design and Application Architecture

INFORMATION_SCHEMA

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-13 : 13:22:08

As a best pracrices type of thing, I thought keeping a data dictionary stored within the database it represents.

So I made 2 tables to hold comments...I wasn't keen on using extended properties, as I want this to be portable for any platform

So I matched the sizes of the columns...and get an error

Warning! The maximum key length is 900 bytes.

The index 'PK__Diversity_Column__62AFA012'
has maximum length of 1024 bytes.

For some combination of large values, the insert/update operation will fail.

How does sql server resolve the issue of uniqueness in the catalog?




CREATE TABLE Diversity_Columns (
       TABLE_CATALOG        nvarchar(128) NOT NULL,
       TABLE_SCHEMA         nvarchar(128) NOT NULL,
       TABLE_NAME           sysname NOT NULL,
       COLUMN_NAME          sysname NOT NULL,
       Comments             varchar(MAX) NULL,
       AddBy                varchar(20) NOT NULL,
       AddDt                datetime NOT NULL DEFAULT (GetDate()),
       UpdBy                varchar(20) NOT NULL,
       UpdDt                datetime NOT NULL DEFAULT (GetDate())
)
go

CREATE INDEX XIF1Diversity_Columns ON Diversity_Columns
(
       TABLE_CATALOG                  ASC,
       TABLE_SCHEMA                   ASC,
       TABLE_NAME                     ASC
)
go


ALTER TABLE Diversity_Columns
       ADD PRIMARY KEY CLUSTERED (COLUMN_NAME ASC, TABLE_SCHEMA ASC, 
              TABLE_NAME ASC, TABLE_CATALOG ASC)
go


CREATE TABLE Diversity_Tables (
       TABLE_CATALOG        nvarchar(128) NOT NULL,
       TABLE_SCHEMA         nvarchar(128) NOT NULL,
       TABLE_NAME           sysname NOT NULL,
       Comments             varchar(MAX) NULL,
       AddBy                varchar(20) NOT NULL,
       AddDt                datetime NOT NULL DEFAULT (GetDate()),
       UpdBy                varchar(20) NOT NULL,
       UpdDt                datetime NOT NULL DEFAULT (GetDate())
)
go


ALTER TABLE Diversity_Tables
       ADD PRIMARY KEY CLUSTERED (TABLE_CATALOG ASC, TABLE_SCHEMA ASC, 
              TABLE_NAME ASC)
go


ALTER TABLE Diversity_Columns
       ADD FOREIGN KEY (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
                             REFERENCES Diversity_Tables  (
              TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
go

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 13:42:27

Its an Nvarchar thing, isn't it?

4 x fields of 128 characters - times 2 for unicode = 1024 bytes.

The reality is that not all of Catalogue / schema / Table / Column will all be 128 characters, so the problem will most likely never arise ??

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-13 : 13:52:32

quote:
Originally posted by Kristen
The reality is that not all of Catalogue / schema / Table / Column will all be 128 characters, so the problem will most likely never arise ??

Ah the M$ Hope and pray method

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-13 : 13:55:12

or I could just change them to varchar(128)

Do you think there'd be any problems joing varchar(128) to nvarchar(128)??

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 14:12:18

I always CAST joins to disparate types (The Optimiser has been know to do a Bad Job with JOINs of different datatypes - e.g. SQL 2000 SP4)

You not got any Chinese columns names? (I'm fairly sure we've got some that are double-Dutch!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources