noob - What''s the easiet way to print a list of database tables and their fields.

Question

noob - What''s the easiet way to print a list of database tables and their fields.

Bajan_mo

SSC Enthusiast

Points: 101
More actions
March 20, 2007 at 2:13 pm

#176531

Hi Guys,
A bit new to SQL Server and I am trying to print a list of all of the tables and their fields using a sql script however its not quite working (See below). The problem seems to be the sp_help stored procedure which when run by it self works fine.
I was also wondering if there was an easier way to do this and if anyone could point me to any good SQL tutorials which could help me with this stuff.
DECLARE my_cursor INSENSITIVE CURSOR
FOR SELECT NAME FROM SYSOBJECTS where TYPE = 'U' order by NAME
DECLARE @t_name varchar(40)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @t_name
WHILE @@FETCH_STATUS = 0
BEGIN
dbo.sp_help @t_name
FETCH NEXT FROM my_cursor
END
CLOSE my_cursor
DEALLOCATE my_cursor
[ERROR MSG]
>[Error] Script lines: 1-25 -------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 13: Incorrect syntax near 'sp_help'.
More exceptions ... [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'END'.
[Executed: 3/20/07 4:08:35 PM VET ] [Execution: 0/ms]
Just beyond your imagination...
Bajan_mo

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

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

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 1

Hi there,

You might want to take a look at the (undocumented) stored procedure sp_MSforeachtable: http://www.sqlservercentral.com/columnists/bknight/sp_msforeachtable.asp

Somthing like:

USE database;

GO

EXECUTE sp_msforeachtable 'EXECUTE sp_help ''?'''

- James

--
James Moore
Red Gate Software Ltd

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

I'm sure James' way is much better but here is what you needed to fix your sql.

DECLARE my_cursor INSENSITIVE CURSOR

FOR SELECT NAME FROM SYSOBJECTS where TYPE = 'U' order by NAME

DECLARE @t_name varchar(40),@sqlstmt varchar(50)

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @t_name

WHILE @@FETCH_STATUS = 0

BEGIN

set @sqlstmt = 'sp_help '+ @t_name

exec (@sqlstmt)

FETCH NEXT FROM my_cursor

END

CLOSE my_cursor

DEALLOCATE my_cursor

John Sinclair Mr or Mrs. 500 Points: 518 More actions · Answer 3

a much MUCH better way is to use the INFORMATION_SCHEMA views

try these to get an idea of what you can do:

select * from information_schema.tables

select * from information_schema.columns

you can filter them with where clauses, just as with any other select statement.

FYI, there are also views for stored procs\functions, and views: information_schema.routines and information_schema.views

Enjoy

Bajan_mo SSC Enthusiast Points: 101 More actions · Answer 4

Hi John,

Thanks alot - I actually like this idea and its a bit cleaner than working with sysobjects. However I am getting an error with getting the script to process the columns:

use train

/* MS SQL syntax. */

DECLARE my_cursor INSENSITIVE CURSOR

FOR SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.tables order by TABLE_NAME

DECLARE @t_name varchar(40),@sqlstmt varchar(200)

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @t_name

WHILE @@FETCH_STATUS = 0

BEGIN

/*

PRINT '-- ' + @t_name + ' --'

PRINT ' '

*/

set @sqlstmt = 'select COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT,' +

'IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, DOMAIN_NAME from INFORMATION_SCHEMA.columns where TABLE_NAME = ' + @t_name

exec (@sqlstmt)

FETCH NEXT FROM my_cursor

END

CLOSE my_cursor

DEALLOCATE my_cursor

[Error]

>[Error] Script lines: 1-28 -------------------------

getMoreResults(): [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'AI_ACCT'.

1 record(s) affected

[Executed: 3/21/07 4:04:16 PM VET ] [Execution: 295/ms]

* the problem here is that AI_ACCT is a valid entry in the column, and I am not sure y its thinking that AI_ACCT is a column. - Some sort of syntax error ?

Just beyond your imagination...

Bajan_mo

John Sinclair Mr or Mrs. 500 Points: 518 More actions · Answer 5

Hi Bajan,

One problem I can see with what you are doing is the table name that is in @t_name will not have quotes around it - so you should add quotes aroudn the value of this variable when you build the SQL.

But thats not what caused your error, not sure about that.

Not sure what you're trying to do, but from the above script it seems you could what you need from a single select from information_schema.columns without mucking around with cursors at all?

I always try and avoid cursors. Besides performing poorly (not a major worry most of the time) they make your sql more complex and harder to debug.

Cheers

John

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

John is right. It's as simple as this:

SELECT

TABLE_SCHEMA + '.' + TABLE_NAME AS [Table],

COLUMN_NAME AS [Column]

FROM INFORMATION_SCHEMA.COLUMNS

ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

If you want extra information, such as data type or nullability, you can include that as well.

John

Bajan_mo SSC Enthusiast Points: 101 More actions · Answer 7

Thanks John²,

The last bit of code worked also, all I have to do is work on the formatting. You see the real problem is that I am trying to document this database that's why I needed to get the fields flowing in that order.

Just beyond your imagination...

Bajan_mo

Camilo Torres SSCrazy Points: 2034 More actions · Answer 8

You want to take this further, if you are documenting the DB?. Read the BOL for the sp_help. Then create a script to output the result set in the order wish, lets assume you want you want a list of tables and all related to the tables (you can get help for all objects in the db, that why you will need to loop thru the sys.objects and order by...)

Once you script output the data you want create a Job and paste your script in the step section, go to the advance tab, output to a log file and you will get all in one file ready for formatting.