USE [quote_cn]
GO

/****** Object:  StoredProcedure [dbo].[OutputData]    Script Date: 06/18/2015 09:52:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/**
 * 参数：表名
 * 输出：指定表下所有记录的insert语句
 */
CREATE PROCEDURE [dbo].[OutputData]     
@tablename   sysname     
AS     
declare    @column   varchar(1000)     
declare    @columndata   varchar(1000)     
declare    @sql   varchar(4000)     
declare    @xtype   tinyint     
declare    @name   sysname     
declare    @objectId   int     
declare    @objectname   sysname     
declare    @ident   int     

set    nocount    on     
set    @objectId=object_id(@tablename)     
if @objectId   is    null    --   判断对象是否存在     
   begin     
      print @tablename + '对象不存在'     
      return     
    end      set @objectname=rtrim(object_name(@objectId))     
if @objectname is null or charindex(@objectname,@tablename)=0
    begin     
      print @tablename + '对象不在当前数据库中'      
      return     
    end        if OBJECTPROPERTY(@objectId,'IsTable')   <   >   1   --   判断对象是否是表     
    begin     
      print @tablename + '对象不是表'     
      return     
    end        select    @ident=status&0x80   from    syscolumns   where    id=@objectid   and    status&0x80=0x80          if @ident is    not    null     
    print    'SET   IDENTITY_INSERT   '+ @TableName + '   ON'     --定义游标，循环取数据并生成Insert语句
declare syscolumns_cursor cursor for 
    select    c.name,c.xtype   from    syscolumns   c     
      where    c.id=@objectid     
      order    by    c.colid     --打开游标
open    syscolumns_cursor     
set @column=''     
set @columndata=''     
fetch    next    from    syscolumns_cursor   into    @name,@xtype     
while    @@fetch_status   <> -1     
    begin     
    if    @@fetch_status   <> -2     
      begin     
      if    @xtype   not    in(189,34,35,99,98)   --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理     
        begin     
        set    @column=@column + 
          case    when    len(@column)=0   then ''  
                 else    ','
                 end + @name     
        set    @columndata = @columndata + 
          case    when    len(@columndata)=0   then    ''   
                 else    ','','','
                 end + 
          case    when @xtype   in(167,175) then    '''''''''+'+@name+'+'''''''''                --varchar,char     
                 when    @xtype   in(231,239)   then    '''N''''''+'+@name+'+'''''''''             --nvarchar,nchar     
                 when    @xtype=61   then    '''''''''+convert(char(23),'+@name+',121)+'''''''''   --datetime     
                 when    @xtype=58   then    '''''''''+convert(char(16),'+@name+',120)+'''''''''   --smalldatetime     
                   when    @xtype=36   then    '''''''''+convert(char(36),'+@name+')+'''''''''       --uniqueidentifier     
                 else    @name   
                 end     
        end     
      end     
    fetch    next    from    syscolumns_cursor   into    @name,@xtype     
    end     
close    syscolumns_cursor     
deallocate    syscolumns_cursor          set @sql='set   nocount   on   select   ''insert   '+@tablename+'('+@column+')   values(''as  ''--'','+@columndata+','')''   from   '+@tablename         print    '--'+@sql     
exec(@sql)         
if    @ident   is    not    null     
print 'SET   IDENTITY_INSERT   '+@TableName+'   OFF'

GO

查询表下所有字段信息
SELECT * FROM SysColumns WHERE id=Object_Id('TableName') ;

查询包含指定字段的表
SELECT object_name(id), * FROM SysColumns WHERE name like 'campaign_code2' ;

-- 查询用户表下所有指定排序规则的表名及其字段清单：
select b.name, a.*
  from syscolumns a, SysObjects b
 where a.id = b.id
   and b.xtype = 'U'
   and a.collation = 'Chinese_PRC_CI_AS'
 order by b.name;

-- 查询所有表名、字段名、类型、长度
select o.name, c.name, t.name, c.length
  from syscolumns c
 inner join systypes t
    on c.xtype = t.xtype
 inner join sysobjects o
    on c.id = o.id
 where o.xtype = 'u'
 order by o.name, c.name, t.name;

-- 修改指定字段排序规则：
ALTER TABLE individual ALTER COLUMN c_ssn nvarchar(255) COLLATE Latin1_General_CI_AS;


-- 批量更新字段排序规则
-- 更新时需指定字段类型，查询条件和生成的脚本需对应上
select o.name,
       c.name,
       t.name,
       c.length,
       c.collation,
       'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name + ' nvarchar(' +
       cast(c.length as varchar) + ') COLLATE Latin1_General_CI_AS;' as tSQL
  from syscolumns c
 inner join systypes t      on c.xtype = t.xtype
 inner join sysobjects o    on c.id = o.id
 where o.xtype = 'u'
   and c.collation = 'Chinese_PRC_CI_AS'
   and t.name = 'nvarchar'
 order by o.name, c.name, t.name;