How can I print table structures in SQLExpress

I need to get a print out of column names, types, and properties for each of the tables. We just started using SQL Express from Access. I cannot find any reference to printing the structures in Microsoft SQL Server Management Studio Express.

Thanks

hi,

this is actually non avaliable at all..

you can have a "similar" result via SQL Server Management Studio Express (http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en) "Diagrams" features.. add all required tables + views and print it out.. not very beautifull but available.. or get them to clipboard and pasting them into Paint or the like...

regards

|||

Sigh............................

Thanks

|||

Rich

Perhaps this will help. Query your database with this:

SELECT * FROM
INFORMATION_SCHEMA.COLUMN

Copy the results into excel or something and get rid of the column you don't need.

|||

Subtle wrote:

Rich

Perhaps this will help. Query your database with this:

SELECT * FROM
INFORMATION_SCHEMA.COLUMN

Copy the results into excel or something and get rid of the column you don't need.

How about using sp_describe_cursor_columns? Then you can print the output from C# or something?

|||

hi,

I'd better go for INFORMATION_SCHEMA ANSI view or, if your really want to go a quiet unsupported way, you could even resort on an unsupported/undocumented stored procedure, sp_MSForEach (http://www.databasejournal.com/features/mssql/article.php/3441031)..

then you can define your query to be executed against all database tables...

for instance something based on catalog views, similar to

SET NOCOUNT ON;

USE AdventureWorks;

GO

DECLARE @.cmd varchar(MAX);

SET @.cmd = 'SELECT ''?'' AS [ObjName];

SELECT clmns.name AS [Name]

, usrt.name AS [DataType], ISNULL(baset.name, N'''') AS [SystemType]

, clmns.is_nullable, clmns.is_identity, clmns.is_rowguidcol, clmns.is_computed

, CASE WHEN clmns.name IN (''timestamp'', ''rowversion'') THEN 1 ELSE 0 END AS is_timestamp

, CASE WHEN clmns.default_object_id <> 0 THEN 1 ELSE 0 END AS [has_default_value]

, CASE WHEN clmns.default_object_id = 0 THEN '''' ELSE

(SELECT d.definition

FROM sys.default_constraints d

WHERE(clmns.object_id = d.parent_object_id)

AND d.parent_column_id = clmns.column_id)

END AS [Default Definition]

, CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length]

, CAST(clmns.precision AS int) AS [NumericPrecision]

, CAST(clmns.scale AS int) AS [NumericScale]

FROM sys.tables AS tbl

INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id

WHERE clmns.object_id = OBJECT_ID(''?'')

ORDER BY clmns.column_id ASC;'

EXEC sp_MSforeachtable @.cmd;

--<--

ObjName

-

[Production].[ProductProductPhoto]

Name DataType SystemType is_nullable is_identity is_rowguidcol is_computed is_timestamp has_default_value Default Definition Length NumericPrecision NumericScale

- -- -- -- - -- -- -- -- -

ProductID int int 0 0 0 0 0 0 4 10 0

ProductPhotoID int int 0 0 0 0 0 0 4 10 0

Primary Flag bit 0 0 0 0 0 1 ((0)) 1 1 0

ModifiedDate datetime datetime 0 0 0 0 0 1 (getdate()) 8 23 3

...other objs..

you are then obviously free to add/remove projections as required..

regards