How do I use variables for tbl & col names in select stmnt

[markgrizzle]

Hi,
I need to build a list of table and column names using sysObjects and sysColumns, based on the contents of a column.

This procedure attempts to loop through each user table's varchar column(s), storing matching values in another table.

My problem is I can't figure out how to use variables for table and column names in my select statement. Can anyone see an error or suggest an alternative?

Thanks,
Mark

CREATE PROCEDURE dbo.z_detective @findstring varchar(256) as

declare @tbl varchar(50)
declare @col varchar(50)
declare @count int

drop table val
create table val
(
tablename varchar(50),
columnname varchar(50),
matches int
)

--build the cursor
declare curse cursor for

select
o.[name] as tbl, c.[name] as col
from
dbo.syscolumns c
inner join
dbo.sysobjects o on c.id = o.id
where
o.type = 'U'
and
c.xtype in (167,35)
and
c.name not in ('CreatedBy','LastEditBy')

--find potential matches
open curse
fetch next from curse into @tbl, @col

while(@@fetch_status <> -1)
begin
select @count = count(*)
from @tbl
where @col = @findstring

if @count > 0
--match found, display result
begin
insert val values(@tbl, @col, @count)
end
fetch next from curse
end

close curse
deallocate curse

select * from val order by matches desc
go

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[SQLDenis]

read

http://www.sommarskog.se/dynamic_sql.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

The answer:

You don't.

Coding this way is Bad. Real Bad. Don't do it.

 

[markgrizzle]

Thanks SQLDenis,
The paper was a big help. I'm working my way through the required changes now.

ESquared,
The procedure isn't for production use. I need to figure out the table structure of a very large legacy application so I can add reporting features. The idea is to have users enter test data into the interface, and then search for that data, revealing which tables are involved.

Having said that, can you recommend an alternate approach?

 

[ESquared]

Okay. I will provisionally agree that this is the sort of use where dynamic SQL can be reasonable. I've done it myself for administration-type code when it was the only way.

Follow Denis's link about dynamic SQL. Basically, you use queries to build queries in strings and then execute them.

Here's an example for how complicated it can get just to do seemingly simple tasks.

 

[markgrizzle]

Here's what I came up with;

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[z_detective]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[z_detective]
GO

CREATE PROCEDURE dbo.z_detective
@string varchar(500) as
/*
loop through all user tables in the database looking for
tables and columns containing @string
*/

declare @table sysname
declare @column sysname
declare @sql varchar(500)

drop table detective
create table detective(tablename varchar(50), columnname varchar(50), matches int)

declare curse cursor for
select o.[name] as tbl, c.[name] as col
from dbo.syscolumns c
inner join dbo.sysobjects o on c.id = o.id
where o.type = 'U' and c.xtype = 167
and c.name not in ('CreatedBy','LastEditBy')
and o.name not like '%log%' and o.name <> 'detective'
and o.name not like '%history'

open curse
fetch next from curse into @table, @column
while(@@fetch_status <> -1)
begin
select @sql =
'select ''' + @table + ''', ''' + @column +
''', count(*) ' +
'from ' + @table + ' ' +
'where ' + @column + ' like ''' + @string + ''' ' +
'group by ' + @column + ' ' +
'having count(*) > 0'

insert into detective exec(@sql)
fetch next from curse into @table, @column
end
deallocate curse

select * from detective order by matches desc

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks again to everyone who responded.
Mark