IT狂想曲~

/*要分步驟看清楚再執行*/
/*在 tempdb 產生一個 table 準備放產生描述的 SQL */
    use tempdb
    GO
    CREATE TABLE [DDSQL] (
  [sid] [int] IDENTITY (1, 1) NOT NULL ,
  [runsql] [nvarchar] (1000) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL CONSTRAINT [DF_DDSQL_runsql] DEFAULT (0),
  [isrun] [bit] NOT NULL CONSTRAINT [DF_DDSQL_isrun] DEFAULT (0),
  CONSTRAINT [PK_DDSQL] PRIMARY KEY  CLUSTERED
  (
      [sid]
  ) WITH  FILLFACTOR = 75  ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    
    
/*進行描述的差異比對 ,  產生描述生成的 SQL type 4 是欄位的描述 , type 3 是 table 的描述 */
    truncate table tempdb..DDSQL;
    insert into tempdb..DDSQL ( runsql )
    select
  case
      when B.type = '4' then
    'sp_addextendedproperty N''' + convert(nvarchar(255),B.name) + ''', N''' + isnull(replace(convert(nvarchar(255),B.value),'''',''''''),'')+ ''', N''user'', N''dbo'', N''table'', N''' + A.Table_name + ''', N''column'', N''' + A.Column_name + ''';'
      when B.type = '3' then
    'sp_addextendedproperty N''' + convert(nvarchar(255),B.name) + ''', N''' + isnull(replace(convert(nvarchar(255),B.value),'''',''''''),'')+ ''', N''user'', N''dbo'', N''table'', N''' + A.Table_name + ''';'
  end [runsql]
    from (
      select dt.name  [Table_name] , '' [Column_name] , P.type , P.name , P.value
      from TESTDB.dbo.SysObjects dt
      left join SysProperties P on
    dt.id = P.id and  dt.xtype = 'U' and P.smallid = 0
      union all
      Select
    dt.name [Table_name], C.name [Column_name]  ,
    P.type , P.name , P.value
      From  ( Select id , name FROM SysObjects WHERE xtype = 'U' ) dt
      Join SysColumns C On C.id=dt.id
      left Join SysProperties P On C.id=P.id and C.colid=P.smallid
    ) A left join (
      select dt.name  [Table_name] , '' [Column_name] , P.type , P.name , P.value
      from [DEMOSQL].TESTDB.dbo.SysObjects dt
      join [DEMOSQL].TESTDB.dbo.SysProperties P on
    dt.id = P.id and  dt.xtype = 'U' and P.smallid = 0
      union all
      Select
    dt.name [Table_name], C.name [Column_name]  ,
    P.type , P.name , P.value
      From  ( Select id , name FROM  [DEMOSQL].TESTDB.dbo.SysObjects WHERE xtype = 'U' ) dt
      Join [DEMOSQL].TESTDB.dbo.SysColumns C On C.id=dt.id
      Join [DEMOSQL].TESTDB.dbo.SysProperties P On C.id=P.id and C.colid=P.smallid
    ) B on
  A.Table_name = B.Table_name and
  A.Column_name = B.Column_name
    where
  A.name is null and B.type in ( 3 , 4 )
    go

    
    
/*用 CURSOR 去 DDSQL 抓 sql 出來執行*/
    declare @RunSql nvarchar(1000);
    declare @sid int;
    declare @I int;

    DECLARE TableName CURSOR
    FOR
  select sid , runsql  from tempdb..DDSQL where isrun = 0 order by sid

    OPEN TableName

    FETCH NEXT FROM TableName INTO @sid , @RunSql
    WHILE @@FETCH_STATUS = 0
    BEGIN
  print '/*'+convert(nvarchar(100),@sid) + '*/'+ @RunSql;
  exec(@RunSql);
  update  tempdb..DDSQL set isrun = 1 where sid = @sid
  FETCH NEXT FROM TableName INTO @sid , @RunSql
    END

    CLOSE TableName
    DEALLOCATE TableName    

    
    
    
/*清空現有DB所有描述 , 看情狀執行*/
    sp_configure 'allow updates','1'  
    go  
    reconfigure with override  
    go  

    delete SysProperties

    sp_configure 'allow updates','0'  
    go  
    reconfigure with override
    go