if exists (select a.name from syscolumns a join sysobjects b on (a.id=b.id and b.id=object_id('YourTableName')) where a.name='ColName') print 'YES' else print 'NO'
麻烦: if exists( select * from syscolumns a where a.id=object_id('tablename') and name='colname') print 'yes' else print 'no'
--很简单 declare @len int select @len= COL_LENGTH( 'table' , 'field1' ) if @len is null print 'not exist' else print ' exist'
create proc test @table varchar(20) @field varchar(20) as if exists(select @field from @table) print 'yes' else print 'no'
USE table if 'field1' in(SELECT COL_NAME(object_id('table'), ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table') print '存在' else print '不存在'
USE table if 'field1' in(SELECT COL_NAME(object_id('table'), ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table') print '存在' else print '不存在'
declare @tablename nvarchar(30) declare @colname nvarchar(30) Set @tablename='tblBank' Set @colname='BankCode' exec ( 'if exists( select * from syscolumns a where a.id=object_id(''' +@tablename+''') and name='''+@colname+''') ' + ' print ''yes'' '+ ' else '+ ' print ''no'' ')
print 'YES'
else
print 'NO'
if exists( select * from syscolumns a where a.id=object_id('tablename') and name='colname')
print 'yes'
else
print 'no'
如果@var的值在列field1里本身并不存在,那么返回'not exists'
declare @len int
select @len= COL_LENGTH( 'table' , 'field1' )
if @len is null
print 'not exist'
else
print ' exist'
@table varchar(20)
@field varchar(20)
as
if exists(select @field from @table)
print 'yes'
else
print 'no'
if 'field1' in(SELECT COL_NAME(object_id('table'), ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table')
print '存在'
else
print '不存在'
if 'field1' in(SELECT COL_NAME(object_id('table'), ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table')
print '存在'
else
print '不存在'
declare @tablename nvarchar(30)
declare @colname nvarchar(30)
Set @tablename='tblBank'
Set @colname='BankCode'
exec ( 'if exists( select * from syscolumns a where a.id=object_id(''' +@tablename+''') and name='''+@colname+''') ' +
' print ''yes'' '+
' else '+
' print ''no'' ')