Sql to create tables listing

Question

Sql to create tables listing

irene.leung

Mr or Mrs. 500

Points: 502
More actions
December 29, 2009 at 3:06 pm

#228772

I have a database on SQL 2005. I would like to get help with sql to create a listing of its table names, its associated column names, keys, create date and row counts all on one document if possible.
Any input is appreciated

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

You can start by running the standard Disk Usage by Tables report. This report will not give you columns for the table - but gives you everything else you are looking for.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

irene.leung Mr or Mrs. 500 Points: 502 More actions · Answer 2

Can you please show me how to run the standard Disk Usage by Tables report ?

Many Thanks

S3ssion SSChasing Mays Points: 602 More actions · Answer 3

select *

from information_schema.routines

Select*

From Information_schema.Tables

| If in Doubt...don't do it!! |

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Right-click on the database, select Reports | Standard Reports | Disk Usage by Tables

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mmathews 87112 SSC Journeyman Points: 97 More actions · Answer 5

Here's another option...

USE EnterYourDBNameHere

GO

SELECT *

FROM sys.Tables

GO

Hope this helps - Mark Mathews

pahunt78 Newbie Points: 1 More actions · Answer 6

mmathews 87112 (1/3/2010)
Here's another option...
USE EnterYourDBNameHere
GO
SELECT *
FROM sys.Tables
GO
Hope this helps - Mark Mathews

But that won't list the columns or keys associated with the table so it doesn't really answer the question asked

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 7

like folks have commented, it is a start. the question originally asked, did not have enough specifics, it was broad and high level and looked like needing some direction.

anyways, these other tables can be joined to sys.tables to get some additional info:

sys.columns, sys.types, sys.indexes, sys.index_columns.

it is probably a good idea to read and understand more about these tables from SQL Server Books On Line.

also, something like this has been done numerous times and if you search enough you can probably find a script out somewhere. but i would strongly recommend to spend some time reading and understanding the above tables first.

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 8

I'm not entirely sure what you need or what you are after...In terms of table space used you can try, amongst others, the tip provided in this link:

http://www.mssqltips.com/tip.asp?tip=1177

In terms of table information this little undocumented stored proc can be useful to loop around all tables in a database, and SP_Help maywell contain alot of the information that you need.

exec sp_MSforeachtable

'sp_help''?'''

You can also use stored proc to get a row count of each table:

create table #NoRows (tablename varchar(128), NoRows int)

exec sp_MSforeachtable 'insert into #NoRows select ''?'', count(*) from ?'

select * from #NoRows

order by NoRows

drop table #NoRows

I'm not sure if this answers your question entirely but hopefully it helps.

Gethyn Elliswww.gethynellis.com

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 9

Here are some FAQ queries asked out of the SQL Server System Catalog

Querying the SQL Server System Catalog FAQ - 2008

Querying the SQL Server System Catalog FAQ - 2005