April 18, 2004 at 8:51 pm
I am using the Information Schema to retrive a list of tables and their columns:
SELECT TABLE_NAME, COLUMN_NAME,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.columns
ORDER BY TABLE_NAME, ORDINAL_POSITION
This works fine but I get all the tables when I just want the user tables. This their any way to limit the results to just these tables. Also is their a way to determine what columns are primary keys. What other information schema would be useful?
Thanks for your help in advance.
Thanks Brian
You are never an expert, you are always learning!April 20, 2004 at 2:58 am
This Link will get you started retrieving metadata from sql server:
http://www.dbazine.com/sharma4.shtml
/rockmoose
You must unlearn what You have learnt
April 20, 2004 at 4:07 am
To filter the user tables in your sql
SELECT TABLE_NAME, COLUMN_NAME,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.columns
WHERE OBJECTPROPERTY( OBJECT_ID(TABLE_CATALOG + '..' + TABLE_NAME), 'IsMsShipped' ) = 0
ORDER BY TABLE_NAME, ORDINAL_POSITION
/rockmoose
You must unlearn what You have learnt
April 20, 2004 at 12:02 pm
The above will also return views which i'm not sure if you want or not. You could write it like this just for users base tables.
SELECT c.TABLE_NAME, COLUMN_NAME,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.columns c INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_NAME = t.TABLE_NAME and TABLE_TYPE = 'BASE TABLE'
ORDER BY c.TABLE_NAME, ORDINAL_POSITION
April 20, 2004 at 12:08 pm
The following will list primary and foreign keys and the columns they use.
select TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
ORDER BY TABLE_NAME, CONSTRAINT_NAME
April 20, 2004 at 5:42 pm
Thank you all
Thanks Brian
You are never an expert, you are always learning!Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply