left join (select [tb_name], [idx_name],count(1) col_num from #idxtmp group by [tb_name], [idx_name]) b
on a.tb_name=b.tb_name
and a.idx_name=b.[idx_name]
--打开游标
open mycursor
--从游标里取出数据赋值到我们刚才声明的2个变量中
fetch next from mycursor into @tblname,@tbcomments,@colno,@colname,@IsIdt,@ispk,@type,@length,@decim,@isnull,@default,@comments
--判断游标的状态
-- 0 fetch语句成功
---1 fetch语句失败或此行不在结果集中
---2 被提取的行不存在
while (@@fetch_status=0)
begin
--显示出我们每次用游标取出的值
--print '游标成功取出一条数据'
if @colno=1
begin
set @tbl=@tblname
set @sql='CREATE TABLE [dbo].['+@tblname+'](
['+@colname+'] ['+@type+'] '+(case @isnull when 0 then 'NOT NULL,' else 'NULL,'end)
set @sql6='EXEC sys.sp_addextendedproperty @name=N'+''''+'MS_Description'+''',@value=N'+''''+@tbcomments+''',@level0type=N'+'''SCHEMA'+''',@level0name=N'+'''dbo'
set @sql5='EXEC sys.sp_addextendedproperty @name=N'+'''MS_Description'+''', @value=N'+''''+@comments+''',@level0type=N'+''''+'SCHEMA'+''',@level0name=N'
if @colname in('ETL_CRC','QA_RULE_CHK_FLG','QA_MANUAL_FLG','CREATE_BY','UPDATE_BY')
begin
set @sql=@sql+''
end
else
begin
set @sql=@sql+'
'+'['+@colname+'] ['+(case @type
when 'timestamp' then 'bigint'+']'
when 'varchar' then @type +']' +'('+cast(@length as varchar(10))+')'
when 'nvarchar' then @type +']' +'('+cast(@length as varchar(10))+')'
when 'char' then @type +']' +'('+cast(@length as varchar(10))+')'
when 'decimal' then @type +']'+'('+cast(@length as varchar(10))+','+cast(@decim as varchar(3))+')'
else @type+']'end )+
(case @isnull when 0 then ' NOT NULL,' else ' NULL,'end)
set @sql5='EXEC sys.sp_addextendedproperty @name=N'+'''MS_Description'+''', @value=N'+''''+@comments+''',@level0type=N'+''''+'SCHEMA'+''',@level0name=N'