SELECT * FROM Syscolumns WHERE ID=OBJECT_ID('表名')

/*=============================================*/


--2 实现全角与半角字符转换的处理函数
CREATE FUNCTION f_Convert(
@str NVARCHAR(4000), --要转换的字符串
@flag bit              --转换标志,0转换成半角,1转换成全角
)RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @pat nvarchar(8),@step int,@i int,@spc int
    IF @flag=0
        SELECT @pat=N'%[！-～]%',@step=-65248,
            @str=REPLACE(@str,N'　',N' ')
    ELSE
        SELECT @pat=N'%[!-~]%',@step=65248,
            @str=REPLACE(@str,N' ',N'　')
    SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
    WHILE @i>0
        SELECT @str=REPLACE(@str,
                SUBSTRING(@str,@i,1),
                NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
            ,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
    RETURN(@str)
END
GO

declare T_cursor cursor local for
select 
    a.Name,    b.Name 
from 
    sysobjects a 
join 
    syscolumns b on a.ID=b.ID
join 
    systypes c on c.xusertype=b.Xtype
where
    a.xtype='U' and c.Name in('nvarchar','nchar','varchar','char')
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin 
    exec('update '+@tabName+' set '+@ColName+'=dbo.f_Convert('+@ColName+',0) where PATINDEX(N''%[！-～]%'' COLLATE LATIN1_GENERAL_BIN'+','+@ColName+')>0'
    fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor

--改列的数据全角为半角
declare T_cursor cursor local for
select 
    a.Name,    b.Name 
from 
    sysobjects a 
join 
    syscolumns b on a.ID=b.ID
join 
    systypes c on c.xusertype=b.Xtype
where
    a.xtype='U' and c.Name in('nvarchar','nchar','varchar','char')
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin 
    exec('update '+@tabName+' set '+@ColName+'=dbo.f_Convert('+@ColName+',0) where PATINDEX(N''%[！-～]%'' COLLATE LATIN1_GENERAL_BIN'+','+@ColName+')>0')--少了)
    fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor
go
--改列名全角为半角
declare T_cursor cursor local for
select 
    a.Name,    b.Name 
from 
    sysobjects a 
join 
    syscolumns b on a.ID=b.ID
where
    a.xtype='U' and PATINDEX(N'%[！-～]%' COLLATE LATIN1_GENERAL_BIN,b.Name)>0
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin 
    exec('exe sp_rename '''+@tabName+'.'+@ColName+''',''dbo.f_Convert('+@ColName+',0)''')
    fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor

--改列名全角为半角
declare T_cursor cursor local for
select 
    a.Name+'.'+b.Name,NameNew=dbo.f_Convert(b.Name)
from 
    sysobjects a 
join 
    syscolumns b on a.ID=b.ID
where
    a.xtype='U' and PATINDEX(N'%[！-～]%' COLLATE LATIN1_GENERAL_BIN,b.Name)>0
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin 
    exe sp_rename @tabName,@ColName
    fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor

declare @columnsname varchar(30) 
declare @tablename varchar(20) 

set @tablename='dbo.t_kycl' 
declare getcolumnsname cursor for 
select a.name from syscolumns a,sysobjects b 
where a.id=b.id and b.xtype='u' and a.id=@tablename 

open getcolumnsname 

fetch next from getcolumnsname into @columnsname 
--上面这行提示错误： 
--消息 245，级别 16，状态 1，第 11 行 
--在将 varchar 值 'dbo.t_kycl' 转换成数据类型 int 时失败。 

while @@fetch_status=0 
begin 

print @columnsname 

fetch next from getcolumnsname into @columnsname 
end 

close getcolumnsname 
deallocate getcolumnsname 

USE AdventureWorks
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @LastName varchar(50), @FirstName varchar(50)

DECLARE contact_cursor CURSOR FOR
SELECT LastName, FirstName FROM Person.Contact
WHERE LastName LIKE 'B%'
ORDER BY LastName, FirstName

OPEN contact_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 

FETCH NEXT FROM contact_cursor
INTO @LastName, @FirstName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
   PRINT 'Contact Name: ' + @FirstName + ' ' +  @LastName

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM contact_cursor
   INTO @LastName, @FirstName
END

CLOSE contact_cursor
DEALLOCATE contact_cursor
GO

declare @columnsname varchar(30) 
declare @tablename varchar(20) 

set @tablename='dbo.t_kycl' 
declare getcolumnsname cursor for 
select a.name from syscolumns a,sysobjects b 
where a.id=b.id and b.xtype='u' and b.name=@tablename 

open getcolumnsname 
fetch next from getcolumnsname into @columnsname 

while @@fetch_status=0 
begin 
fetch next from getcolumnsname into @columnsname 
    print @columnsname
end 

close getcolumnsname 
deallocate getcolumnsname