views:

93

answers:

3

Is there a standard method to retrieve the 'CREATE TABLE..' definition of a table in SQL Server?

I can ask INFORMATION_SCHEMA for definitions of views and functions/procedures, so I thought it would be natural to get the same for tables, but I didn't find anything.

+3  A: 

Yes - INFORMATION_SCHEMA.TABLES should be what you're looking for.

It will give you something like:

aspnetdb    dbo    aspnet_Paths                      BASE TABLE
aspnetdb    dbo    aspnet_PersonalizationAllUsers    BASE TABLE
aspnetdb    dbo    aspnet_PersonalizationPerUser     BASE TABLE
aspnetdb    dbo    vw_aspnet_WebPartState_Paths      VIEW
aspnetdb    dbo    vw_aspnet_WebPartState_Shared     VIEW
aspnetdb    dbo    vw_aspnet_WebPartState_User       VIEW
aspnetdb    dbo    aspnet_Applications               BASE TABLE

If you want to know about the columns of a table, look at INFORMATION_SCHEMA.COLUMNS.

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'aspnet_Users'

will give you all the details for the columns for the table specified.

marc_s
I know that I can find alle the information in those tables, but I was looking for an automatic way to retrieve the complete create-script including all constraints for a table, just as I can get it when working in MSSQL Management Studio.
Homer J. Simpson
I don't think you can just "find" the complete create script anywhere in any of the system catalog views. If you want a complete script, you need to use SMO, as Nestor suggested - it has a function to script out any kind of database object - which probably just inspects those system catalog views and concatenates together the info into a full create script.
marc_s
A: 

You can use SMO (Microsoft.SqlServer.Smo) from .NET to script the table.

Nestor
oh I see.. sorry, i didnt understand your question.
Nestor
+1  A: 

Using INFORMATION_SCHEMA for views and code will fail. The data is limited to nvarchar(4000), so longer stuff will not be read. Use sys.sql_modules or OBJECT_DEFINITION.

For tables, it's more difficult. A "table" consists of columns, constraints, indexes, possibly rules, UDTs (did I forget anything?). This is why SSMS has so many table scripting options compared to a view or stored proc.

I'd suggest profiling SSMS and hope it doesn't use SMO... or use SMO via CLR code or even xp_cmdshell.

gbn
Thanks, valuable information.
Homer J. Simpson
NVARCHAR(MAX) *rocks*. Ah, the hoops we had to jump through in SQL2K....
Peter