字段 select name from syscolumns where id=object_id('表名')
--获取当前数据库所有表和字段 declare @table_name nvarchar(384) declare @table_owner nvarchar(384) declare @table_qualifier sysname declare @column_name nvarchar(384) declare @ODBCVer int --AS DECLARE @full_table_name nvarchar(769) DECLARE @table_id int if @ODBCVer <> 3 select @ODBCVer = 2 if @column_name is null /* If column name not supplied, match all */ select @column_name = '%' if @table_qualifier is not null begin if db_name() <> @table_qualifier begin /* If qualifier doesn't match current database */ raiserror (15250, -1,-1) return end end if @table_name is null begin /* If table name not supplied, match all */ select @table_name = '%' end if @table_owner is null begin /* If unqualified table name */ SELECT @full_table_name = quotename(@table_name) end else begin /* Qualified table name */ if @table_owner = '' begin /* If empty owner name */ SELECT @full_table_name = quotename(@table_owner) end else begin SELECT @full_table_name = quotename(@table_owner) + '.' + quotename(@table_name) end end /* Get Object ID */ SELECT @table_id = object_id(@full_table_name) if ((isnull(charindex('%', @full_table_name),0) = 0) and (isnull(charindex('[', @table_name),0) = 0) and (isnull(charindex('[', @table_owner),0) = 0) and (isnull(charindex('_', @full_table_name),0) = 0) and @table_id <> 0) begin /* this block is for the case where there is no pattern matching required for the table name */
SELECT TABLE_QUALIFIER = convert(sysname,DB_NAME()), TABLE_OWNER = convert(sysname,USER_NAME(o.uid)), TABLE_NAME = convert(sysname,o.name), COLUMN_NAME = convert(sysname,c.name), d.DATA_TYPE, convert (sysname,case when t.xusertype > 255 then t.name else d.TYPE_NAME collate database_default end) TYPE_NAME, convert(int,case when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ else OdbcPrec(c.xtype,c.length,c.xprec) end) "PRECISION", convert(int,case when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */ OdbcPrec(c.xtype,c.length,c.xprec)+2 else isnull(d.length, c.length) end) LENGTH, SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)), d.RADIX, NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')), REMARKS = convert(varchar(254),null), /* Res are NULL */ COLUMN_DEF = text, d.SQL_DATA_TYPE, d.SQL_DATETIME_SUB, CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin, ORDINAL_POSITION = convert(int, ( select count(*) from syscolumns sc where sc.id = c.id AND sc.number = c.number AND sc.colid <= c.colid )), IS_NULLABLE = convert(varchar(254), substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)), SS_DATA_TYPE = c.type into TableFieldList FROM sysobjects o, master.dbo.spt_datatype_info d, systypes t, syscolumns c LEFT OUTER JOIN syscomments m on c.cdefault = m.id AND m.colid = 1 WHERE o.id = @table_id AND c.id = o.id AND t.xtype = d.ss_dtype AND c.length = isnull(d.fixlen, c.length) AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0)) AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0) AND c.xusertype = t.xusertype AND c.name like @column_name ORDER BY 17 end else begin /* this block is for the case where there IS pattern matching done on the table name */ if @table_owner is null /* If owner not supplied, match all */ select @table_owner = '%' SELECT TABLE_QUALIFIER = convert(sysname,DB_NAME()), TABLE_OWNER = convert(sysname,USER_NAME(o.uid)), TABLE_NAME = convert(sysname,o.name), COLUMN_NAME = convert(sysname,c.name), d.DATA_TYPE, convert (sysname,case when t.xusertype > 255 then t.name else d.TYPE_NAME collate database_default end) TYPE_NAME, convert(int,case when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ else OdbcPrec(c.xtype,c.length,c.xprec) end) "PRECISION", convert(int,case when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */ OdbcPrec(c.xtype,c.length,c.xprec)+2 else isnull(d.length, c.length) end) LENGTH, SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)), d.RADIX, NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')), REMARKS = convert(varchar(254),null), /* Res are NULL */ COLUMN_DEF = text, d.SQL_DATA_TYPE, d.SQL_DATETIME_SUB, CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin, ORDINAL_POSITION = convert(int, ( select count(*) from syscolumns sc where sc.id = c.id AND sc.number = c.number AND sc.colid <= c.colid )), IS_NULLABLE = convert(varchar(254), rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3))), SS_DATA_TYPE = c.type into TableFieldList FROM sysobjects o, master.dbo.spt_datatype_info d, systypes t, syscolumns c LEFT OUTER JOIN syscomments m on c.cdefault = m.id AND m.colid = 1 WHERE o.name like @table_name AND user_name(o.uid) like @table_owner AND o.id = c.id AND t.xtype = d.ss_dtype AND c.length = isnull(d.fixlen, c.length) AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0)) AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0) AND c.xusertype = t.xusertype AND c.name like @column_name ORDER BY 2, 3, 17 endGO 调用如下: select * from TableFieldList where type_name in ('char','nvarchar')第二步就比较麻烦啦。
1.取得表名 select name from sysobjects where xtype='U'2.将表名写入游标3.依次从游标中取得表名,并取得该表所有Varchar与Char字段 select name from syscolumns where id=object_id('表名') and xtype in (175,167)4.用动态语句拼写更新5.游标取得下一个表名......
declare @tablename varchar(200) declare @tableid numeric(18,0) declare @column_name varchar(20) declare @column_type numeric(18,0) --取得所有表 declare tables cursor for select name from sysobjects where xtype = 'U' open tables fetch next from tables into @tablename while @@fetch_status = 0 begin select @tableid = id from sysobjects where name = @tablename --取表中所有字段 declare table_columns cursor for select name,xtype from syscolumns where id = @tableid open table_columns fetch next from table_columns into @column_name,@column_type while @@fetch_status = 0 begin if @column_type = 175 or @column_type = 167 begin --使用动态sql,用@tablename和@column_name更新空格 end end close table_columns deallocate table_columns fetch next from table_pam into @tablename end close tables deallocate tables
谢谢 gahade!但是倒数第4行fetch next from table_pam into @tablename是什么意思, 看不懂??
应该是名为tables的游标的误写
是不是 应该改成这样declare @tablename varchar(200) declare @tableid numeric(18,0) declare @column_name varchar(20) declare @column_type numeric(18,0) --取得所有表 declare tables cursor for select name from sysobjects where xtype = 'U' open tables fetch next from tables into @tablename while @@fetch_status = 0 begin select @tableid = id from sysobjects where name = @tablename --取表中所有字段 declare table_columns cursor for select name,xtype from syscolumns where id = @tableid open table_columns fetch next from table_columns into @column_name,@column_type while @@fetch_status = 0 begin if @column_type = 175 or @column_type = 167 begin print @tablename + @column_name end fetch next from table_columns into @column_name,@column_type end close table_columns deallocate table_columns fetch next from tables into @tablename end close tables deallocate tables
应该没错了declare @tablename varchar(200) declare @tableid numeric(18,0) declare @column_name varchar(20) declare @column_type numeric(18,0) --取得所有表 declare tables cursor for select name from sysobjects where xtype = 'U' and Name like 'a%' AND LEN(NAME)=8 Order By Name open tables fetch next from tables into @tablename while @@fetch_status = 0 begin select @tableid = id from sysobjects where name = @tablename --取表中所有字段 declare table_columns cursor for select name,xtype from syscolumns where id = @tableid open table_columns fetch next from table_columns into @column_name,@column_type while @@fetch_status = 0 begin if @column_type = 175 or @column_type = 167 begin print @tablename + ' ' + @column_name Exec ('Update ' + @tablename + ' Set ' + @column_name + '=rtrim(' + @column_name + ')') end fetch next from table_columns into @column_name,@column_type end close table_columns deallocate table_columns fetch next from tables into @tablename end close tables deallocate tables
select name from syscolumns where id=object_id('表名')
--获取当前数据库所有表和字段
declare @table_name nvarchar(384)
declare @table_owner nvarchar(384)
declare @table_qualifier sysname
declare @column_name nvarchar(384)
declare @ODBCVer int
--AS
DECLARE @full_table_name nvarchar(769)
DECLARE @table_id int
if @ODBCVer <> 3
select @ODBCVer = 2
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @table_name is null
begin /* If table name not supplied, match all */
select @table_name = '%'
end
if @table_owner is null
begin /* If unqualified table name */
SELECT @full_table_name = quotename(@table_name)
end
else
begin /* Qualified table name */
if @table_owner = ''
begin /* If empty owner name */
SELECT @full_table_name = quotename(@table_owner)
end
else
begin
SELECT @full_table_name = quotename(@table_owner) +
'.' + quotename(@table_name)
end
end /* Get Object ID */
SELECT @table_id = object_id(@full_table_name)
if ((isnull(charindex('%', @full_table_name),0) = 0) and
(isnull(charindex('[', @table_name),0) = 0) and
(isnull(charindex('[', @table_owner),0) = 0) and
(isnull(charindex('_', @full_table_name),0) = 0) and
@table_id <> 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */
SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Res are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)),
SS_DATA_TYPE = c.type
into TableFieldList
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.id = @table_id
AND c.id = o.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @column_name
ORDER BY 17
end
else
begin
/* this block is for the case where there IS pattern
matching done on the table name */ if @table_owner is null /* If owner not supplied, match all */
select @table_owner = '%' SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Res are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3))),
SS_DATA_TYPE = c.type
into TableFieldList
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.name like @table_name
AND user_name(o.uid) like @table_owner
AND o.id = c.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @column_name
ORDER BY 2, 3, 17
endGO
调用如下:
select * from TableFieldList where type_name in ('char','nvarchar')第二步就比较麻烦啦。
select name from sysobjects where xtype='U'2.将表名写入游标3.依次从游标中取得表名,并取得该表所有Varchar与Char字段
select name from syscolumns where id=object_id('表名') and xtype in (175,167)4.用动态语句拼写更新5.游标取得下一个表名......
declare @tablename varchar(200)
declare @tableid numeric(18,0)
declare @column_name varchar(20)
declare @column_type numeric(18,0)
--取得所有表
declare tables cursor for
select name from sysobjects where xtype = 'U'
open tables
fetch next from tables into @tablename
while @@fetch_status = 0
begin
select @tableid = id from sysobjects where name = @tablename
--取表中所有字段
declare table_columns cursor for
select name,xtype from syscolumns where id = @tableid
open table_columns
fetch next from table_columns into @column_name,@column_type
while @@fetch_status = 0
begin
if @column_type = 175 or @column_type = 167
begin
--使用动态sql,用@tablename和@column_name更新空格
end
end
close table_columns
deallocate table_columns
fetch next from table_pam into @tablename
end
close tables
deallocate tables
declare @tableid numeric(18,0)
declare @column_name varchar(20)
declare @column_type numeric(18,0)
--取得所有表
declare tables cursor for
select name from sysobjects where xtype = 'U'
open tables
fetch next from tables into @tablename
while @@fetch_status = 0
begin
select @tableid = id from sysobjects where name = @tablename
--取表中所有字段
declare table_columns cursor for
select name,xtype from syscolumns where id = @tableid
open table_columns
fetch next from table_columns into @column_name,@column_type
while @@fetch_status = 0
begin
if @column_type = 175 or @column_type = 167
begin
print @tablename + @column_name
end
fetch next from table_columns into @column_name,@column_type
end
close table_columns
deallocate table_columns
fetch next from tables into @tablename
end
close tables
deallocate tables
declare @tableid numeric(18,0)
declare @column_name varchar(20)
declare @column_type numeric(18,0)
--取得所有表
declare tables cursor for
select name from sysobjects where xtype = 'U' and Name like 'a%' AND LEN(NAME)=8 Order By Name
open tables
fetch next from tables into @tablename
while @@fetch_status = 0
begin
select @tableid = id from sysobjects where name = @tablename
--取表中所有字段
declare table_columns cursor for
select name,xtype from syscolumns where id = @tableid
open table_columns
fetch next from table_columns into @column_name,@column_type
while @@fetch_status = 0
begin
if @column_type = 175 or @column_type = 167
begin
print @tablename + ' ' + @column_name
Exec ('Update ' + @tablename + ' Set ' + @column_name + '=rtrim(' + @column_name + ')')
end
fetch next from table_columns into @column_name,@column_type
end
close table_columns
deallocate table_columns
fetch next from tables into @tablename
end
close tables
deallocate tables