SunQuest
           MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!
  #1  
Old June 22nd, 2004, 10:25 AM
timmyd timmyd is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2004
Posts: 8 timmyd User rank is Just a Lowly Private (1 - 20 Reputation Level) 
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

Reply With Quote
  #2  
Old June 22nd, 2004, 12:07 PM
timmyd timmyd is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2004
Posts: 8 timmyd User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Forgot to mention.

By the way, I forgot to mention that the above query will probably be a view named metaData. It would then be called using this query:

Code:
SELECT * FROM metaData WHERE tableName = '<table_to_get_meta_data_from>'

Tim Davis
The Hobbies of Tim Davis

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > Metadata speed issue.


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway