February 20, 2007 at 5:06 am
Hi All,
I am working on databases without a schema at the moment. Therefore I am locating fields and tables using this SQL query.
SELECT DISTINCT sc.Name as FieldName, so.Name as TableName
FROM sysobjects so WITH (NOLOCK)
INNER JOIN syscolumns sc WITH (NOLOCK) ON so.id = sc.id
WHERE so.type ='U'
AND sc.name like'%ticket%'
I would like to turn this into a SP or Function where I can pass a field name in (e.g. ticket - as above) and the query returns a list of Select [FieldName] from [TableName]. This means I can then copy the results set and find the data I am looking for quickly.
I have a had a little play around and considered using a table variable but I cannot work out how to return the results set.
Thanks.
February 20, 2007 at 5:39 am
Create Procedure usp_MyProc @Ticket Nvarchar(50)
as
SELECT DISTINCT sc.Name as FieldName, so.Name as TableName
FROM sysobjects so WITH (NOLOCK)
INNER JOIN syscolumns sc WITH (NOLOCK) ON so.id = sc.id
WHERE so.type ='U'
AND sc.name like @Ticket
Then call the your usp as:
exec usp_MyProc 'TableName'
How's that go for you?
February 20, 2007 at 6:12 am
Adam
This will work in SQL Server 2005 as well:
CREATE PROCEDURE MyProc @colname sysname
AS
SELECT 'select [' + column_name + '] from [' + table_schema + '].[' + table_name + ']'
FROM information_schema.columns
WHERE column_name = @colname
GO
Call it like this:
EXEC MyProc 'MyCol'
It will return SELECT MyCol FROM owner.MyTable for every value of owner.MyTable.
John
February 20, 2007 at 6:41 am
That's brilliant John. Thanks for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply