June 1, 2005 at 9:32 am
I know using T-SQL the sp_tables and sp_columns stored procedure will display the necessary table and column information. Unfortunately I need to be able get this kind of information through an ODBC link to a variety of databases.
Is there and standard SQL command that will retrieve table and column information? I would have thought there must be some way as a tool like Microsoft Query will display column and table information.
Anybody got any ideas?
Many thanks,
James Knight
June 1, 2005 at 9:55 am
i don't know if this is what you are looking for, but you can always query the information_schema views (information_schema.tables, information_schema.columns, etc.).
June 2, 2005 at 3:14 am
That actually works better that the stored procedures for SQL server databases, but it doesn't work for other odbc databases (eg Access). Is there a generic odbc command that will list table and column information?
Thanks,
James Knight
June 2, 2005 at 5:07 am
You can create two temperal tabels, #Column and #Table, that have same column as sp_columns and sp_tables return. Then,
INSERT #Column EXEC sp_column
INSERT #Table EXEC sp_tables
Other databases call two tables and get data
July 22, 2005 at 3:51 am
Personally, I create a view that gives me a select from the meta data. The TOP 100 PERCENT allows you to do an ORDER BY in a view. You can then link it in Access as a data source.
CREATE VIEW dbo.vzTableColumnNames AS
SELECT TOP 100 PERCENT *
FROM INFORMATION_SCHEMA.COLUMNS
--WHERE (table_name LIKE 'Ed%') AND (table_name NOT LIKE 'Edz%')
ORDER BY table_name, ordinal_position
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply