
June 22nd, 2004, 10:25 AM
|
|
Registered User
|
|
Join Date: Jun 2004
Posts: 8
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Metadata speed issue.
The query below is designed to get information from the INFORMATION_SCHEMA views available in MS SQL. It gets the data I want, but it takes a second or two to load which is unacceptable. I'm sure there is a way to streamline this data so that it loads faster but I just can't seem to figure out the best way to handle it. Any help would be appreciated!
(Note: One of the sub-queries gets data from a table I created called variables. It is only included for sake of completeness.)
Code:
SELECT
COL.TABLE_NAME AS tableName,
COL.COLUMN_NAME AS columnName,
COL.ORDINAL_POSITION AS ordinalPosition,
COL.COLUMN_DEFAULT AS columnDefault,
COL.IS_NULLABLE AS isNullable,
COL.DATA_TYPE AS dataType,
COL.CHARACTER_MAXIMUM_LENGTH AS stringSize,
VARS.USER_ID AS hiddenUserId,
CON.FK_COLUMN_NAME AS foreignKey,
CON.UQ_TABLE_NAME AS uniqueTableName,
CON.UQ_COLUMN_NAME AS uniqueKey
FROM
INFORMATION_SCHEMA.COLUMNS COL
LEFT OUTER JOIN
(SELECT
VARS.table_name,
VARS.column_name AS HIDDEN,
VARS.user_id AS USER_ID
FROM
variables VARS,
[user] USERS
WHERE
(USERS.id = VARS.user_id OR
USERS.id = 0) AND
VARS.type = 'hide') VARS
ON
COL.TABLE_NAME = VARS.table_name AND
COL.COLUMN_NAME = VARS.HIDDEN
LEFT OUTER JOIN
(SELECT
KCU1.TABLE_NAME AS FK_TABLE_NAME,
KCU1.COLUMN_NAME AS FK_COLUMN_NAME,
KCU2.TABLE_NAME AS UQ_TABLE_NAME,
KCU2.COLUMN_NAME AS UQ_COLUMN_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
WHERE
KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND
KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME AND
KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND
KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME) CON
ON
COL.TABLE_NAME = CON.FK_TABLE_NAME AND
COL.COLUMN_NAME = CON.FK_COLUMN_NAME
ORDER BY COL.ORDINAL_POSITION
Tim Davis
The Hobbies of Tim Davis
|