Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hannah00
Starting Member
31 Posts |
 Posted - 2005-04-26 : 17:39:16
|
| Hi all,I am Oracle DBA so I don't know much about MSSQL data dictionary liek Oracle had. Any way, may question is I want to write a query that retrieve back all of the columns in the revelant tables with its datatype and length and the table name itsslef. I know I have to use syscolumns, join with sys objects but I don't undertsand the detail on each column inside these two tables to I can't come up with the query.could someone please help??? |
|
|
jason
Posting Yak Master
164 Posts |
Posted - 2005-04-26 : 17:56:21
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48890 |
 |
|
|
kish
Starting Member
45 Posts |
Posted - 2005-04-27 : 07:22:25
|
| sp_help 'tablename' |
|
|
|
hannah00
Starting Member
31 Posts |
Posted - 2005-04-27 : 09:32:28
|
| I didn't get the answer spicific to my question. can someone please help??? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-27 : 10:14:28
|
The view INFORMATION_SCHEMA.COLUMNS is probably a better source for the data you are looking for. It's a lot more user friendly and easier for figure out without the documentation. The various INFORMATION_SCHEMA views are where you should look for SQL Server metadata, because Microsoft may change the underlying tables in future releases.Whether you decide to use this or syscolumns and sysobjects, the documentation is in SQL Server Books Online.-- Change to pubs databaseuse pubs-- Get column info for authors tableselect *from INFORMATION_SCHEMA.COLUMNSwhere TABLE_CATALOG = 'pubs' and TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'authors'order by ORDINAL_POSITION CODO ERGO SUM |
|
|
|
|
|
|
query to retrieve all of the columns