October 8, 2009 at 12:34 pm
I am wondering, if there is a stored procedure provided by Microsoft that allows the creation of a database object, such as a table, without pointing to the object in SMS and choosing SCRIPT TABLE AS => CREATE TO
Thanks
October 8, 2009 at 1:28 pm
Probably - if you're curious as to how it generates the script, run a Profiler session against your spid when you script object(s) out.
MJM
October 8, 2009 at 2:00 pm
Nice hint looking into utilizing Profiler to get the understanding on how a script is getting composed by SMS
Thanks
October 8, 2009 at 3:20 pm
It is a nice hint. I wonder if Profiler will actually pick it up.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 3:37 pm
Jeff Moden (10/8/2009)
It is a nice hint. I wonder if Profiler will actually pick it up.
Yes sir, it does 😀
Here is what the SSMS UI uses to generate the SQL command (there is some other decisioning done in other calls, but this was the end result on a test table I have out there). Oh yes, this needs to be run in the database context which contains the table you want to generate a CREATE script for. It's a start, anyway.
exec sp_executesql N'SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
clmns.column_id AS [ID],
clmns.name AS [Name],
clmns.is_ansi_padded AS [AnsiPaddingStatus],
clmns.is_computed AS [Computed],
ISNULL(cc.definition,N'''') AS [ComputedText],
usrt.name AS [DataType],
ISNULL(baset.name, N'''') AS [SystemType],
sclmns.name AS [DataTypeSchema],
CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
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],
clmns.is_identity AS [Identity],
CAST(ISNULL(ic.seed_value,0) AS bigint) AS [IdentitySeed],
CAST(ISNULL(ic.increment_value,0) AS bigint) AS [IdentityIncrement],
ISNULL(clmns.collation_name, N'''') AS [Collation],
CAST(clmns.scale AS int) AS [NumericScale],
clmns.is_nullable AS [Nullable],
CAST(clmns.is_filestream AS bit) AS [IsFileStream],
ISNULL(ic.is_not_for_replication, 0) AS [NotForReplication],
(case when clmns.default_object_id = 0 then N'''' when d.parent_object_id > 0 then N'''' else d.name end) AS [Default],
(case when clmns.default_object_id = 0 then N'''' when d.parent_object_id > 0 then N'''' else schema_name(d.schema_id) end) AS [DefaultSchema],
(case when clmns.rule_object_id = 0 then N'''' else r.name end) AS [Rule],
(case when clmns.rule_object_id = 0 then N'''' else schema_name(r.schema_id) end) AS [RuleSchema],
ISNULL(xscclmns.name, N'''') AS [XmlSchemaNamespace],
ISNULL(s2clmns.name, N'''') AS [XmlSchemaNamespaceSchema],
ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
CAST(ISNULL(cc.is_persisted, 0) AS bit) AS [IsPersisted],
CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
CAST(clmns.is_sparse AS bit) AS [IsSparse],
CAST(clmns.is_column_set AS bit) AS [IsColumnSet]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_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) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))
LEFT OUTER JOIN sys.schemas AS sclmns ON sclmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
WHERE
(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
[Table_Schema] ASC,[Table_Name] ASC,[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'your_table_name',@_msparam_1=N'dbo'
MJM
October 8, 2009 at 4:05 pm
VERY cool! Thanks Mark.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 4:19 pm
I've done something similar in my own stored proc; give it a test drive, I use it for some different processes for auditing and stuff; just a handy way to get the table ddl via TSQL:
sp_GetDDL takes a tablename, or optionally schema.tablename, and returns the CREATE TABLE DDL statement, including EVERYTHING...defaults, calculated columns, PK/FK/UQ,constraints, indexes, and a lot more; in a well defined format. I even tweaked it to return extended properties if they are defined agaisnt the table or it's columns as well.
simple to use, it's just exec sp_GetDDL YourTableName
--or
exec sp_getDDL 'Schemaname.YourTableName'
see if that gets you close to what you are after.
Lowell
October 8, 2009 at 8:02 pm
MJM,
Your script give error on 2005 SP2.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'is_sparse'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'is_column_set'.
MJ
October 8, 2009 at 9:33 pm
I should remark that I did this on SQL 2008 EE. SPARSE columns types are new in that version...try comenting out that column or running a profiler session on your instance to get the correct syntax or that version.
MJM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply