Monday, March 30, 2009

search name of table by column name

some times this problem occurs when ur database is too large and u know the name of column but u dont know the name of table it belongs from..so to search the name of table which contain that column, u can use this procedure.....

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[GetTableOfColumn]
--this procedure is to search a table name of column in a database by column name
@ColVar varchar(30) =''
as
begin
Set NoCount On



Select

Convert(Char(75), SysObjects.Name) 'Table Names:',

Convert(Char(75), SysColumns.Name) 'Column Names:'



From SysObjects, SysColumns, SysTypes



Where SysObjects.ID = SysColumns.ID

And SysColumns.xType = SysTypes.xType

And SysColumns.Name like @ColVar



Order by SysObjects.Name Asc



Set NoCount Off

end

Share This!


No comments:

Powered By Blogger · Designed By Seo Blogger Templates