{"id":619,"date":"2017-01-11T12:58:21","date_gmt":"2017-01-11T07:28:21","guid":{"rendered":"http:\/\/mairwa.com\/wordpress\/?p=619"},"modified":"2017-01-11T12:58:36","modified_gmt":"2017-01-11T07:28:36","slug":"get-table-detail","status":"publish","type":"post","link":"http:\/\/mairwa.com\/wordpress\/?p=619","title":{"rendered":"Get Table Detail"},"content":{"rendered":"

DECLARE @table_name SYSNAME<\/p>\n

SELECT @table_name = ‘dbo.TableName’<\/p>\n

 <\/p>\n

DECLARE<\/p>\n

@object_name SYSNAME<\/p>\n

, @object_id INT<\/p>\n

 <\/p>\n

SELECT<\/p>\n

@object_name = ‘[‘ + s.name + ‘].[‘ + o.name + ‘]’<\/p>\n

, @object_id = o.[object_id]<\/p>\n

FROM sys.objects o WITH (NOWAIT)<\/p>\n

JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]<\/p>\n

WHERE s.name + ‘.’ + o.name = @table_name<\/p>\n

AND o.[type] = ‘U’<\/p>\n

AND o.is_ms_shipped = 0<\/p>\n

 <\/p>\n

DECLARE @SQL NVARCHAR(MAX) = ”<\/p>\n

 <\/p>\n

;WITH index_column AS<\/p>\n

(<\/p>\n

SELECT<\/p>\n

ic.[object_id]<\/p>\n

, ic.index_id<\/p>\n

, ic.is_descending_key<\/p>\n

, ic.is_included_column<\/p>\n

, c.name<\/p>\n

FROM sys.index_columns ic WITH (NOWAIT)<\/p>\n

JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id<\/p>\n

WHERE ic.[object_id] = @object_id<\/p>\n

),<\/p>\n

fk_columns AS<\/p>\n

(<\/p>\n

SELECT<\/p>\n

k.constraint_object_id<\/p>\n

, cname = c.name<\/p>\n

, rcname = rc.name<\/p>\n

FROM sys.foreign_key_columns k WITH (NOWAIT)<\/p>\n

JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id<\/p>\n

JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id<\/p>\n

WHERE k.parent_object_id = @object_id<\/p>\n

)<\/p>\n

SELECT @SQL = ‘CREATE TABLE ‘ + @object_name + CHAR(13) + ‘(‘ + CHAR(13) + STUFF((<\/p>\n

SELECT CHAR(9) + ‘, [‘ + c.name + ‘] ‘ +<\/p>\n

CASE WHEN c.is_computed = 1<\/p>\n

THEN ‘AS ‘ + cc.[definition]<\/p>\n

ELSE UPPER(tp.name) +<\/p>\n

CASE WHEN tp.name IN (‘varchar’, ‘char’, ‘varbinary’, ‘binary’, ‘text’)<\/p>\n

THEN ‘(‘ + CASE WHEN c.max_length = -1 THEN ‘MAX’ ELSE CAST(c.max_length AS VARCHAR(5)) END + ‘)’<\/p>\n

WHEN tp.name IN (‘nvarchar’, ‘nchar’, ‘ntext’)<\/p>\n

THEN ‘(‘ + CASE WHEN c.max_length = -1 THEN ‘MAX’ ELSE CAST(c.max_length \/ 2 AS VARCHAR(5)) END + ‘)’<\/p>\n

WHEN tp.name IN (‘datetime2’, ‘time2’, ‘datetimeoffset’)<\/p>\n

THEN ‘(‘ + CAST(c.scale AS VARCHAR(5)) + ‘)’<\/p>\n

WHEN tp.name = ‘decimal’<\/p>\n

THEN ‘(‘ + CAST(c.[precision] AS VARCHAR(5)) + ‘,’ + CAST(c.scale AS VARCHAR(5)) + ‘)’<\/p>\n

ELSE ”<\/p>\n

END +<\/p>\n

CASE WHEN c.collation_name IS NOT NULL THEN ‘ COLLATE ‘ + c.collation_name ELSE ” END +<\/p>\n

CASE WHEN c.is_nullable = 1 THEN ‘ NULL’ ELSE ‘ NOT NULL’ END +<\/p>\n

CASE WHEN dc.[definition] IS NOT NULL THEN ‘ DEFAULT’ + dc.[definition] ELSE ” END +<\/p>\n

CASE WHEN ic.is_identity = 1 THEN ‘ IDENTITY(‘ + CAST(ISNULL(ic.seed_value, ‘0’) AS CHAR(1)) + ‘,’ + CAST(ISNULL(ic.increment_value, ‘1’) AS CHAR(1)) + ‘)’ ELSE ” END<\/p>\n

END + CHAR(13)<\/p>\n

FROM sys.columns c WITH (NOWAIT)<\/p>\n

JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id<\/p>\n

LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id<\/p>\n

LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id<\/p>\n

LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id<\/p>\n

WHERE c.[object_id] = @object_id<\/p>\n

ORDER BY c.column_id<\/p>\n

FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, CHAR(9) + ‘ ‘)<\/p>\n

+ ISNULL((SELECT CHAR(9) + ‘, CONSTRAINT [‘ + k.name + ‘] PRIMARY KEY (‘ +<\/p>\n

(SELECT STUFF((<\/p>\n

SELECT ‘, [‘ + c.name + ‘] ‘ + CASE WHEN ic.is_descending_key = 1 THEN ‘DESC’ ELSE ‘ASC’ END<\/p>\n

FROM sys.index_columns ic WITH (NOWAIT)<\/p>\n

JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id<\/p>\n

WHERE ic.is_included_column = 0<\/p>\n

AND ic.[object_id] = k.parent_object_id<\/p>\n

AND ic.index_id = k.unique_index_id<\/p>\n

FOR XML PATH(N”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”))<\/p>\n

+ ‘)’ + CHAR(13)<\/p>\n

FROM sys.key_constraints k WITH (NOWAIT)<\/p>\n

WHERE k.parent_object_id = @object_id<\/p>\n

AND k.[type] = ‘PK’), ”) + ‘)’\u00a0 + CHAR(13)<\/p>\n

+ ISNULL((SELECT (<\/p>\n

SELECT CHAR(13) +<\/p>\n

‘ALTER TABLE ‘ + @object_name + ‘ WITH’<\/p>\n

+ CASE WHEN fk.is_not_trusted = 1<\/p>\n

THEN ‘ NOCHECK’<\/p>\n

ELSE ‘ CHECK’<\/p>\n

END +<\/p>\n

‘ ADD CONSTRAINT [‘ + fk.name\u00a0 + ‘] FOREIGN KEY(‘<\/p>\n

+ STUFF((<\/p>\n

SELECT ‘, [‘ + k.cname + ‘]’<\/p>\n

FROM fk_columns k<\/p>\n

WHERE k.constraint_object_id = fk.[object_id]<\/p>\n

FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”)<\/p>\n

+ ‘)’ +<\/p>\n

‘ REFERENCES [‘ + SCHEMA_NAME(ro.[schema_id]) + ‘].[‘ + ro.name + ‘] (‘<\/p>\n

+ STUFF((<\/p>\n

SELECT ‘, [‘ + k.rcname + ‘]’<\/p>\n

FROM fk_columns k<\/p>\n

WHERE k.constraint_object_id = fk.[object_id]<\/p>\n

FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”)<\/p>\n

+ ‘)’<\/p>\n

+ CASE<\/p>\n

WHEN fk.delete_referential_action = 1 THEN ‘ ON DELETE CASCADE’<\/p>\n

WHEN fk.delete_referential_action = 2 THEN ‘ ON DELETE SET NULL’<\/p>\n

WHEN fk.delete_referential_action = 3 THEN ‘ ON DELETE SET DEFAULT’<\/p>\n

ELSE ”<\/p>\n

END<\/p>\n

+ CASE<\/p>\n

WHEN fk.update_referential_action = 1 THEN ‘ ON UPDATE CASCADE’<\/p>\n

WHEN fk.update_referential_action = 2 THEN ‘ ON UPDATE SET NULL’<\/p>\n

WHEN fk.update_referential_action = 3 THEN ‘ ON UPDATE SET DEFAULT’<\/p>\n

ELSE ”<\/p>\n

END<\/p>\n

+ CHAR(13) + ‘ALTER TABLE ‘ + @object_name + ‘ CHECK CONSTRAINT [‘ + fk.name\u00a0 + ‘]’ + CHAR(13)<\/p>\n

FROM sys.foreign_keys fk WITH (NOWAIT)<\/p>\n

JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id<\/p>\n

WHERE fk.parent_object_id = @object_id<\/p>\n

FOR XML PATH(N”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’)), ”)<\/p>\n

+ ISNULL(((SELECT<\/p>\n

CHAR(13) + ‘CREATE’ + CASE WHEN i.is_unique = 1 THEN ‘ UNIQUE’ ELSE ” END<\/p>\n

+ ‘ NONCLUSTERED INDEX [‘ + i.name + ‘] ON ‘ + @object_name + ‘ (‘ +<\/p>\n

STUFF((<\/p>\n

SELECT ‘, [‘ + c.name + ‘]’ + CASE WHEN c.is_descending_key = 1 THEN ‘ DESC’ ELSE ‘ ASC’ END<\/p>\n

FROM index_column c<\/p>\n

WHERE c.is_included_column = 0<\/p>\n

AND c.index_id = i.index_id<\/p>\n

FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”) + ‘)’<\/p>\n

+ ISNULL(CHAR(13) + ‘INCLUDE (‘ +<\/p>\n

STUFF((<\/p>\n

SELECT ‘, [‘ + c.name + ‘]’<\/p>\n

FROM index_column c<\/p>\n

WHERE c.is_included_column = 1<\/p>\n

AND c.index_id = i.index_id<\/p>\n

FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”) + ‘)’, ”)\u00a0 + CHAR(13)<\/p>\n

FROM sys.indexes i WITH (NOWAIT)<\/p>\n

WHERE i.[object_id] = @object_id<\/p>\n

AND i.is_primary_key = 0<\/p>\n

AND i.[type] = 2<\/p>\n

FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’)<\/p>\n

), ”)<\/p>\n

 <\/p>\n

PRINT @SQL<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

DECLARE @table_name SYSNAME SELECT @table_name = ‘dbo.TableName’   DECLARE @object_name SYSNAME , @object_id INT   SELECT @object_name = ‘[‘ + s.name + ‘].[‘ + o.name + ‘]’ , @object_id = o.[object_id] FROM sys.objects o WITH (NOWAIT) JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id] WHERE s.name + ‘.’ + o.name = @table_name AND o.[type] = ‘U’ AND o.is_ms_shipped =… Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[],"class_list":["post-619","post","type-post","status-publish","format-standard","hentry","category-sql","xfolkentry"],"_links":{"self":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/619","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=619"}],"version-history":[{"count":1,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/619\/revisions"}],"predecessor-version":[{"id":620,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/619\/revisions\/620"}],"wp:attachment":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=619"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}