Write a SP to search Field name in a Database in sql server 2005?

Write a SP to SEARCH Field name in a Database in sql server 2005?

To run the SP
/*
findColumnInfo 'cat'
*/

create procedure findColumnInfo
(
@colInfo varchar(100)
)
as
begin

SELECT table_name = sysobjects.name,
column_name = syscolumns.name,
datatype = systypes.name,
length = syscolumns.length
FROM
sysobjects
JOIN
syscolumns ON sysobjects.id = syscolumns.id
JOIN
systypes ON syscolumns.xtype = systypes.xtype
WHERE
sysobjects.xtype='U' and syscolumns.name like '%' + @colInfo + '%'
ORDER BY
sysobjects.name, syscolumns.colid
end

No comments: