How to get columns from a schema.table?

[David Thielen]

Hi;

To get all columns from Sql Server I use:

cmd.CommandText = "SELECT COLUMN_NAME, DATA_TYPE from
INFORMATION_SCHEMA.COLUMNS where table_name=@p1";

But how can I do this if I have a schema.table and there are 2
identically named tables in the database?

Same question for getting FKK mappings (I use):
cmd.CommandText = "SELECT fc.name AS foreign_column_1,
fc2.name AS foreign_column_2, p.name AS primary_table, " +
"rc.name AS primary_column_1, rc2.name AS
primary_column_2 FROM sys.sysobjects AS f INNER JOIN " +
"sys.sysobjects AS c ON f.parent_obj = c.id INNER
JOIN sys.sysreferences AS r ON f.id = r.constid INNER JOIN " +
"sys.sysobjects AS p ON r.rkeyid = p.id INNER JOIN
sys.syscolumns AS rc ON r.rkeyid = rc.id AND " +
"r.rkey1 = rc.colid INNER JOIN sys.syscolumns AS
fc ON r.fkeyid = fc.id AND r.fkey1 = fc.colid LEFT OUTER JOIN " +
"sys.syscolumns AS rc2 ON r.rkeyid = rc2.id AND
r.rkey2 = rc.colid LEFT OUTER JOIN sys.syscolumns AS fc2 ON " +
"r.fkeyid = fc2.id AND r.fkey2 = fc.colid WHERE
(f.type = 'F') AND (c.name = '" + sysTable + "')";

and column descriptions:
cmd.CommandText = "SELECT t.name AS [Table Name], ex.value AS
Description FROM " + sysTable +
" AS t,
sys.extended_properties AS ex WHERE ex.major_id = t.object_id AND
ex.minor_id=0 AND ex.name = 'MS_Description' ";

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[William Vaughn MVP]

Ah, I would not depend on any of these internal views or tables to extract
schema. Instead, I would use the GetSchema Command method for the SqlClient
provider. I expect these would give you a more consistent view of the
schema.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[David Thielen]

Are the parameters for this documented anywhere? What I need to get
is:

1) All tables & views (including it's schema if it has one).
1b) Optionally with each table/view, it's description.
1c) If possible, get just non-system tables or get all (both options).
2) For a table/view, get all columns.
2b) For each column get it's data type, description, and if a FK the
table.column of the PK (schema.table.column) it points to.
3) All stored procedures.
3b) For each stored procedure, all parameters and the return type.
3c) For each stored procedure, it's name, data type, and if it's
IN/OUT.

thanks - dave

Ah, I would not depend on any of these internal views or tables to extract
schema. Instead, I would use the GetSchema Command method for the SqlClient
provider. I expect these would give you a more consistent view of the
schema.

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[David Thielen]

Ah, I would not depend on any of these internal views or tables to extract
schema. Instead, I would use the GetSchema Command method for the SqlClient
provider. I expect these would give you a more consistent view of the
schema.

Hi;

This provides incomplete information. Specifically:

1) Does not provide the description for a table, view, or column.
2) No way to determine if a view is a system or user view.
3) No listing of possible values for DATA_TYPE (or how they map to
DbType).
4) Columns in a table are not returned in metadata order (which users
expect).
5) No way to get PK:FK mapping. GetSchema("ForeignKeys") tells me that
FK_Orders_Customers is set in dbo.Orders but does no give the FK
column and does not give the table or column of the PK it maps to.

So this does not work. I'll post new distinct questions.

Details on the params for GetSchema() are at
http://msdn.microsoft.com/en-us/library/cc716722.aspx

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm