Script to Return all User Tables, Including Column Names

Question

Post reply

Script to Return all User Tables, Including Column Names

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Here is a query that uses INFORMATION_SCHEMA that will run fine on 2000 and 2005. (Option 1 from ColdCoffee).

SELECT t.TABLE_CATALOG,

t.TABLE_SCHEMA,

t.TABLE_NAME,

t.TABLE_TYPE,

c.TABLE_CATALOG,

c.TABLE_SCHEMA,

c.TABLE_NAME,

c.COLUMN_NAME,

c.ORDINAL_POSITION,

c.COLUMN_DEFAULT,

c.IS_NULLABLE,

c.DATA_TYPE,

c.CHARACTER_MAXIMUM_LENGTH,

c.CHARACTER_OCTET_LENGTH,

c.NUMERIC_PRECISION,

c.NUMERIC_PRECISION_RADIX,

c.NUMERIC_SCALE,

c.DATETIME_PRECISION,

c.CHARACTER_SET_CATALOG,

c.CHARACTER_SET_SCHEMA,

c.CHARACTER_SET_NAME,

c.COLLATION_CATALOG,

c.COLLATION_SCHEMA,

c.COLLATION_NAME,

c.DOMAIN_CATALOG,

c.DOMAIN_SCHEMA,

c.DOMAIN_NAME

FROM INFORMATION_SCHEMA.tables t

INNER JOIN INFORMATION_SCHEMA.columns c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA

AND t.TABLE_NAME = c.TABLE_NAME

-- 'BASE TABLE' will filter out VIEWS but not system tables, however in a typical user DB

-- there won't be any system tables

WHERE t.TABLE_TYPE = 'BASE TABLE'

ORDER BY t.TABLE_SCHEMA,

t.TABLE_NAME,

c.COLUMN_NAME ;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

LutzM SSC Guru Points: 107049 More actions · Answer 2

On a slightly different subject:

Is there any specific reason to go from SQL 2000 to 2005 less then a month before the end of the mainstream support of 2005?

By now I would definitely go for 2008...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

David Fulton-420388 SSC Veteran Points: 239 More actions · Answer 3

@ Jack, Your query worked perfectly on the 2000 db. Thank you.

@ opc.three, I'll try your Schema method. Thank you.

@ Lutz, I agree, but those decisions are above my pay grade. Maybe they got a good deal on 2005? :hehe:

Thanks again everyone!

Best regards,

Dave Fulton

WayneS SSC Guru Points: 95460 More actions · Answer 4

You might want to consider adding the schema name to the query that Lutz posted:

SELECT s.name AS schema_name,

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.schemas s

ON s.schema_id = t.schema_id

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

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

WayneS (3/30/2011)
You might want to consider adding the schema name to the query that Lutz posted:
SELECT s.name AS schema_name,
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.schemas s
ON s.schema_id = t.schema_id
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

When dealing with >= SQL2005 - yes for the query that Wayne re-posted.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events