{"id":1908,"date":"2011-12-08T00:03:04","date_gmt":"2011-12-08T00:03:04","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=1908"},"modified":"2011-12-08T00:03:04","modified_gmt":"2011-12-08T00:03:04","slug":"%e8%8e%b7%e5%8f%96%e8%a1%a8%e7%bb%93%e6%9e%84%e7%9a%84sql","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2011\/12\/08\/%e8%8e%b7%e5%8f%96%e8%a1%a8%e7%bb%93%e6%9e%84%e7%9a%84sql\/","title":{"rendered":"\u83b7\u53d6\u8868\u7ed3\u6784\u7684sql"},"content":{"rendered":"<pre escaped=\"true\" lang=\"sql\">\r\n\r\nEXEC GetTableScript 'frmuser'\r\n\r\nCREATE PROCEDURE GetTableScript \r\n@TableName VARCHAR(50)\r\nAS \r\n\r\n--To get table script\r\ndeclare @Id int, @i int, @i2 int,@Sql varchar(max),@Sql2 varchar(max), @f1 varchar(5), @f2 varchar(5), @f3 varchar(5), @f4 varchar(5), @T varchar(5)\r\n    select @Id=object_id(@TableName), @f1 = char(13) + char(10), @f2 = '    ', @f3=@f1+@f2, @f4=',' + @f3\r\n    \r\n    if not(@Id is null)\r\n    BEGIN\r\n    declare @Data table(Id int identity primary key, D varchar(max) not null, ic int null, re int null, o int not null);\r\n    \r\n    -- Columns\r\n    with c as(\r\n        select c.column_id, Nr = row_number() over(order by c.column_id), Clr=count(*) over(),\r\n            D = quotename(c.name) + ' ' +\r\n                case when s.name = 'sys' or c.is_computed=1 then '' else quotename(s.name) + '.' end +\r\n                case when c.is_computed=1 then '' when s.name = 'sys' then t.Name else quotename(t.name) end +\r\n                case when c.user_type_id!=c.system_type_id or c.is_computed=1 then ''\r\n                    when t.Name in ('xml', 'uniqueidentifier', 'tinyint', 'timestamp', 'time', 'text', 'sysname', 'sql_variant', 'smallmoney', 'smallint', 'smalldatetime', 'ntext', 'money',\r\n                                    'int', 'image', 'hierarchyid', 'geometry', 'geography', 'float', 'datetimeoffset', 'datetime2', 'datetime', 'date', 'bigint', 'bit') then ''\r\n                    when t.Name in('varchar','varbinary', 'real', 'numeric', 'decimal', 'char', 'binary')\r\n                        then '(' + isnull(convert(varchar,nullif(c.max_length,-1)), 'max') + isnull(','+convert(varchar,nullif(c.scale, 0)), '') + ')'\r\n                    when t.Name in('nvarchar','nchar')\r\n                        then '(' + isnull(convert(varchar,nullif(c.max_length,-1) \/ 2), 'max') + isnull(','+convert(varchar,nullif(c.scale, 0)), '') + ')'\r\n                    else '??'\r\n                    end + \r\n                case when ic.object_id is not null then ' identity(' + convert(varchar,ic.seed_value) + ',' + convert(varchar,ic.increment_value) + ')' else '' end +\r\n                case when c.is_computed=1 then 'as' + cc.definition when c.is_nullable = 1 then ' null' else ' not null' end +\r\n                case c.is_rowguidcol when 1 then ' rowguidcol' else '' end +\r\n                case when d.object_id is not null then ' default ' + d.definition else  '' end\r\n        from sys.columns c\r\n        inner join sys.types t\r\n        on t.user_type_id = c.user_type_id\r\n        inner join sys.schemas s\r\n        on s.schema_id=t.schema_id\r\n        left outer join sys.computed_columns cc\r\n        on cc.object_id=c.object_id and cc.column_id=c.column_id\r\n        left outer join sys.default_constraints d\r\n        on d.parent_object_id=@id and d.parent_column_id=c.column_id\r\n        left outer join sys.identity_columns ic\r\n        on ic.object_id=c.object_id and ic.column_id=c.column_id\r\n        where c.object_id=@Id\r\n        \r\n    )\r\n        insert into @Data(D, o)\r\n        select '    ' + D + case Nr when Clr then '' else ',' + @f1 end, 0\r\n        from c where NOT D IS NULL \r\n        order by column_id\r\n    \r\n    -- SubObjects\r\n    set @i=0\r\n    while 1=1\r\n        begin\r\n        select top 1 @i=c.object_id, @T = c.type, @i2=i.index_id\r\n        from sys.objects c \r\n        left outer join sys.indexes i\r\n        on i.object_id=@Id and i.name=c.name\r\n        where parent_object_id=@Id and c.object_id&gt;@i and c.type not in('D')\r\n        order by c.object_id\r\n        if @@rowcount=0 break\r\n        if @T = 'C' \r\n            insert into @Data \r\n            select @f4 + 'check ' + case is_not_for_replication when 1 then 'not for replication ' else '' end + definition, null, null, 10\r\n            from sys.check_constraints where object_id=@i\r\n        else if @T = 'Pk'\r\n            insert into @Data \r\n            select @f4 + 'primary key' + isnull(' ' + nullif(lower(i.type_desc),'clustered'), ''), @i2, null, 20\r\n            from sys.indexes i\r\n            where i.object_id=@Id and i.index_id=@i2\r\n        else if @T = 'uq'\r\n            insert into @Data values(@f4 + 'unique', @i2, null, 30)\r\n        else if @T = 'f'\r\n            begin\r\n            insert into @Data \r\n            select @f4 + 'foreign key', -1, @i, 40\r\n            from sys.foreign_keys f\r\n            where f.object_id=@i\r\n            \r\n            insert into @Data \r\n            select ' references ' + quotename(s.name) + '.' + quotename(o.name), -2, @i, 41\r\n            from sys.foreign_keys f\r\n            inner join sys.objects o\r\n            on o.object_id=f.referenced_object_id\r\n            inner join sys.schemas s\r\n            on s.schema_id=o.schema_id\r\n            where f.object_id=@i\r\n            \r\n            insert into @Data \r\n            select ' not for replication', -3, @i, 42\r\n            from sys.foreign_keys f\r\n            inner join sys.objects o\r\n            on o.object_id=f.referenced_object_id\r\n            inner join sys.schemas s\r\n            on s.schema_id=o.schema_id\r\n            where f.object_id=@i and f.is_not_for_replication=1\r\n            end\r\n        else\r\n            insert into @Data values(@f4 + 'Unknow SubObject [' + @T + ']', null, null, 99)\r\n        end\r\n\r\n    insert into @Data values(@f1+')', null, null, 100)\r\n    \r\n    -- Indexes\r\n    insert into @Data\r\n    select @f1 + 'create ' + case is_unique when 1 then 'unique ' else '' end + lower(s.type_desc) + ' index ' + 'i' + convert(varchar, row_number() over(order by index_id)) + ' on ' + quotename(sc.Name) + '.' + quotename(o.name), index_id, null, 1000\r\n    from sys.indexes s\r\n    inner join sys.objects o\r\n    on o.object_id=s.object_id\r\n    inner join sys.schemas sc\r\n    on sc.schema_id=o.schema_id\r\n    where s.object_id=@Id and is_unique_constraint=0 and is_primary_key=0 and s.type_desc != 'heap'\r\n    \r\n    -- columns\r\n    set @i=0\r\n    while 1=1\r\n        begin\r\n        select top 1 @i=ic from @Data where ic&gt;@i order by ic \r\n        if @@rowcount=0 break\r\n        select @i2=0, @Sql=null, @Sql2=null\r\n        while 1=1\r\n            begin\r\n            select @i2=index_column_id, \r\n                @Sql = case c.is_included_column when 1 then @Sql else isnull(@Sql + ', ', '(') + cc.Name + case c.is_descending_key when 1  then ' desc' else '' end end,\r\n                @Sql2 = case c.is_included_column when 0 then @Sql2 else isnull(@Sql2 + ', ', '(') + cc.Name + case c.is_descending_key when 1  then ' desc' else '' end end\r\n            from sys.index_columns c\r\n            inner join sys.columns cc\r\n            on c.column_id=cc.column_id and cc.object_id=c.object_id\r\n            where c.object_id=@Id and index_id=@i and index_column_id&gt;@i2\r\n            order by index_column_id\r\n            if @@rowcount=0 break\r\n            end\r\n        update @Data set D=D+@Sql +')' + isnull(' include' + @Sql2 + ')', '') where ic=@i\r\n        end\r\n        \r\n    -- references\r\n    set @i=0\r\n    while 1=1\r\n        begin\r\n        select top 1 @i=re from @Data where re&gt;@i order by re\r\n        if @@rowcount=0 break\r\n        \r\n        select @i2=0, @Sql=null, @Sql2=null\r\n        while 1=1\r\n            begin\r\n            select @i2=f.constraint_column_id, \r\n                @Sql = isnull(@Sql + ', ', '(') + c1.Name,\r\n                @Sql2 = isnull(@Sql2 + ', ', '(') + c2.Name\r\n            from sys.foreign_key_columns f\r\n            inner join sys.columns c1\r\n            on c1.column_id=f.parent_column_id and c1.object_id=f.parent_object_id\r\n            inner join sys.columns c2\r\n            on c2.column_id=f.referenced_column_id and c2.object_id=f.referenced_object_id\r\n            where f.constraint_object_id=@i and f.constraint_column_id&gt;@i2\r\n            order by f.constraint_column_id\r\n            if @@rowcount=0 break\r\n            end\r\n        update @Data set D = D + @Sql + ')'  where re=@i and ic=-1\r\n        update @Data set D = D + @Sql2 + ')'  where re=@i and ic=-2\r\n        end;\r\n    \r\n    -- Render\r\n    with x as(\r\n        select id=d.id-1, D=d.D + isnull(d2.D,'')\r\n        from @Data d\r\n        left outer join @Data d2\r\n        on d.re=d2.re and d2.o=42\r\n        where d.o=41\r\n        \r\n    )\r\n    update @Data\r\n        set D=d.D+x.D\r\n    from @Data d\r\n    inner join x\r\n    on x.id=d.id\r\n    \r\n    delete @Data where o in(41, 42)\r\n    \r\n    select @Sql = 'create table ' + quotename(s.name) + '.' + quotename(o.name) + '(' + @f1\r\n    from sys.objects o\r\n    inner join sys.schemas s\r\n    on o.schema_id = s.schema_id\r\n    where o.object_id=@Id\r\n    \r\n    set @i=0\r\n    while 1=1\r\n        begin\r\n        select top 1 @I=Id, @Sql = @Sql + D from @Data order by o, case when o=0 then right('0000' + convert(varchar,id),5)  else D end, id\r\n        if @@rowcount=0 break\r\n        delete @Data where id=@i\r\n        end\r\n    END\r\n    SELECT @Sql\r\n\r\n\u5f15\u81ea\uff1ahttp:\/\/www.cnblogs.com\/unruledboy\/archive\/2011\/12\/05\/SQLMon2.html\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>EXEC GetTableScript &#8216;frmuser&#8217; CREATE PROCEDURE GetTable [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-1908","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1908","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=1908"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1908\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=1908"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=1908"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=1908"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}