March 30, 2011 at 1:32 pm
Hi,
I hope everyone is doing well.
I am a LITLE ( 😉 ) over my head, and hope someone here can help.
I am trying to find a way to document all tables in a SQL Server 2005 DB. For each table, I need a list of all columns. Column attributes such as data type, max length, is nullable, etc would be nice if possible. I am only interested in the db structure, and do not need the data itself.
Any ideas would be greatly appreciated.
Best regards,
Dave
March 30, 2011 at 1:44 pm
Something like this?
SELECT t.name AS table_name,
c.name AS column_name,
y.name AS column_type,
c.max_length,
c.precision,
c.scale,
c.collation_name
FROM sys.tables t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types y
ON c.user_type_id=y.user_type_id
WHERE TYPE ='U'
ORDER BY table_name, c.column_id
March 30, 2011 at 1:46 pm
Two ways (there may be more ways :-))
1. Using the INFORMATION_SCHEMA Views such as information_schema.columns, information_schema.tables
2. Using sys.objects, sys.tables and sys.columns SYSTEM views. JOIN these tables to get what you were after
March 30, 2011 at 1:48 pm
LutzM (3/30/2011)
Something like this?
SELECT t.name AS table_name,c.name AS column_name,
y.name AS column_type,
c.max_length,
c.precision,
c.scale,
c.collation_name
FROM sys.tables t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types y
ON c.user_type_id=y.user_type_id
WHERE TYPE ='U'
ORDER BY table_name, c.column_id
You , Mr.Lutz , gave away the query ! 🙂
David Fullton, the above query was what i was saying as my step 2..
March 30, 2011 at 1:51 pm
March 30, 2011 at 1:51 pm
Lutz and ColdCoffee,
Thank you for replying.
Unfortunately, Lutz's query returns this:
"[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error"
Thank you.
Dave
March 30, 2011 at 2:02 pm
David Fulton-420388 (3/30/2011)
Lutz and ColdCoffee,Thank you for replying.
Unfortunately, Lutz's query returns this:
"[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error"
Thank you.
Dave
Please provide more details about how you're using the query. It runs just fine when I tested it with SSMS.
It seems like you're using a different method to call the query.
March 30, 2011 at 2:05 pm
Hi Lutz,
I'm executing it with Query Analyzer.
Thanks.
Dave
March 30, 2011 at 2:05 pm
I don't see anything in the query that would cause that error. I ran it in SSMS against SQL Server 2005 without error. Exactly how are you calling it? Are you using SQL Server 2005 or newer?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 2:10 pm
David Fulton-420388 (3/30/2011)
Hi Lutz,I'm executing it with Query Analyzer.
Thanks.
Dave
Errmmm - Query Analyzer?
You're not using SQL Server 2005 or beyond then? (that's what I assumed based on the forum you posted in...)
March 30, 2011 at 2:21 pm
Now I feel rather silly.
The db that we are migrating into is 2005. The old db, the one that I'm trying to get the information from is 2000. I've been flipping between the two and I guess I got confused. My apologies.
So, I guess I need to re-post my question in another forum.
Thanks.
Dave
March 30, 2011 at 2:25 pm
David Fulton-420388 (3/30/2011)
Now I feel rather silly.The db that we are migrating into is 2005. The old db, the one that I'm trying to get the information from is 2000. I've been flipping between the two and I guess I got confused. My apologies.
So, I guess I need to re-post my question in another forum.
Thanks.
Dave
I'm not sure if you have to re-post it....
@ Jack / ColdCoffee: do you have a SS2K version around or do you remember the table and column names?
March 30, 2011 at 2:30 pm
LutzM (3/30/2011)
@ Jack / ColdCoffee: do you have a SS2K version around or do you remember the table and column names?
No Lutz, i dont have.. sorry.. i couldnt remember the tables/views in SQL 2000 cuz i never worked with it 😀 :blush:
March 30, 2011 at 2:41 pm
This is it for 2000.
SELECT t.name AS table_name,
c.name AS column_name,
y.name AS column_type,
c.length,
c.prec,
c.scale,
c.collation
FROM sysobjects t
INNER JOIN syscolumns c
ON t.id = c.id
INNER JOIN systypes y
ON C.xusertype = y.xusertype
WHERE T.TYPE ='U'
ORDER BY table_name, c.colid
You could also use the INFORMATION_SCHEMA views which will work on both 2000 & 2005
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 2:43 pm
Mr. Lutz,
Your query worked perfectly on my 2005 db. Thanks.
@everyone, any help with getting this info from my 2000 db would be appreciated. If I need to re-post to the correct forum, then I'll do that too.
Thanks again to everyone!
Dave
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply