Tuesday, June 22, 2010

Finding a column name which contain x

This will list all columns in the table tblName of interest which contain the letter x, along with the dataType and length:
select  tableName=tbl.name ,columnName=c.name, typeName=t.name, length= case when t.name='nvarchar' then c.length/2 else c.length end
from  sysobjects tbl 
left join syscolumns c 
 join systypes t on c.xtype=t.xtype and t.name != 'sysname'
 on c.id=tbl.id
where tbl.name like '%tblName%' 
and c.name like '%x%' 

Using this, you can find columns of interest in tables or views.

This was tested against SQL Server 2000.

No comments: