January 11, 2005 at 9:31 am
I'm sure this is a simple question but I have been asked to produce a printed list of table properties, (datatype, null, column names etc.) for all user tables in a database. Is there a stored procedure to do this or some other utility? Any help will be greatly appreciated...
Ken
January 11, 2005 at 10:14 am
A starter for 10 would be to generate scripts for all tables in the database to a text file.
You can do this by right clicking the database and choosing generate SQL script. Then choose Show All and choose tables. Then choose OK and save the output to a text file.
Not too sure whether thas what you want.
Hope this helps
Carl
January 11, 2005 at 12:30 pm
Carl
Yes that will do the trick. Thanks!
Ken
January 12, 2005 at 10:32 am
| You can create a diagram, this is more visual. |
January 12, 2005 at 12:12 pm
Thanks Markus for the suggestion..
January 12, 2005 at 1:02 pm
You can also query the system tables depending on how you want to use it. Visually a diagram is often more helpful, but this can be handy if you need a report or some such.
You can put a loop around it to get the tables you need, and tailor the fields to retrieve just what you are looking for.
/*
Retrieves all the fields and datatype names from
the specified owner and table.
*/
DECLARE @tableName varchar(100)
DECLARE @ownerName varchar(50)
SET @tableName = 'MyTable'
SET @ownerName = 'dbo'
SELECT sc.*, ty.[name] DataType FROM dbo.syscolumns sc
JOIN dbo.sysobjects so ON sc.[id] = so.[id]
JOIN dbo.systypes ty ON sc.xtype = ty.xtype
JOIN dbo.sysusers su ON so.uid = su.uid
WHERE so.[name] = @tableName
AND su.[name] = @ownerName
ORDER BY sc.[colid]
January 12, 2005 at 1:56 pm
another way is to simply issue:
sp_help '<tablename>' where <tablename is the name of your table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply