staring at a blank wall - help ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

staring at a blank wall - help ?

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-05-28 : 10:44:32

Hi guys 'n gals,

This is frustrating. I thought I had this script working, but now it doesn't

There are probably better ways of what I am trying to do.


--This script will create a stored procedure "sp_find_col" 
--in the master database, so that the SP can be call from 
--each database on the instance. Working through the databases 
--is done by a undocumented SP_MSFOREACHDB stored proc.
--
--Created By:	Regan Galbraith
--Created On:	2004-05-27
--Version:	1.0
--
--USAGE: Find and replace (Ctrl+h) on the replacement "tag" below
--
--TAG:	server
--
-- Please NOTE not to save a replace version, 
-- since that can cause issues in re-use in future
if not (object_id('sp_find_col') is NULL) 
begin 
    drop procedure sp_find_col -- 1  
end 
go
create procedure sp_find_col @col_nm sysname
as
set @col_nm = '%'+@col_nm+'%'
set nocount on
	select db_name()
	print @col_nm
	SELECT left(table_catalog,30) as DB,left(table_schema,10) as OWNER,
		left(table_name,40) as Table_Name, left(column_name,30) as Col_Nm
		FROM information_schema.Columns where upper(column_name) like ''+@col_nm+''
--	if (@@rowcount = 0)
--		Print 'Column "'+@col_nm+'" is NOT FOUND'
go
set nocount on
go

sp_msforeachdb @command1='exec sp_find_col server'

if not (object_id('sp_find_col') is NULL) 
begin 
    drop procedure sp_find_col -- 1  
end 
go

When it runs, it only exec's against master, so for each DB found, it then searches master ! grrr. I thought that the whole idea behind sp_ named stored procedures is that they can be called from other databases... can someone point out the (probably obvious) problem here ?

TIA

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mr_mist
Grunnio

1870 Posts

Posted - 2004-05-28 : 10:49:19

You need to specify a USE command with your replacement character, like USE [ ? ] (without the spaces) for example, then execute the command in that context

@command1='use [ ? ]; exec sp_find_col server',@replacechar = '?'

I may have the syntax wrong.

-------
Moo. :)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-05-28 : 11:19:53

I'm afraid that didn't do it :-(
I tried it, and exactly the same results as mine.

If you run the code I posted, you should see the database that the SP is running against in the report <<the select db_name()>> line. But when executing the select from information_schema, it refuses to use the db, and instead hits master.

Another possible solution might be to get the information_schema prefixed explicitly with the DBname, but somehow I couldn't get that working either

thoughts ?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-05-28 : 11:22:54

PS : Moo - the syntax was only slightly wrong - don't put the spaces around the ?.
and not


[ ? ]

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-01 : 03:04:37

Yus, except if you write a squarebracketquestionmarksquarebracket here you get this

To help with your problem, it could be that you have to pass through to your procedure the database name as an argument, so that you keep context. Eg.

@command1='use [ ? ]; exec sp_find_col server

',@replacechar = '?'

-------
Moo. :)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-01 : 03:40:46

Yeah - I found out about the

when trying to reply to your's [:-)]

I used that syntax - I can see that inside the SP, the database context is correct, and yet the information_schema vies referenced are the master ones.

Sample code running for a search for "email" columns:

use master 
go 
if not (object_id('dbo.sp_find_col') is NULL) 
begin 
    drop procedure dbo.sp_find_col -- 1  
end 
go
create procedure dbo.sp_find_col @col_nm sysname
as
set @col_nm = '%'+@col_nm+'%'
set nocount on
	select db_name()
	print @col_nm
	SELECT left(table_catalog,30) as DB,left(table_schema,10) as OWNER,
		left(table_name,40) as Table_Name, left(column_name,30) as Col_Nm
		FROM information_schema.Columns where upper(column_name) like ''+@col_nm+''
--	if (@@rowcount = 0)
--		Print 'Column "'+@col_nm+'" is NOT FOUND'
go
set nocount on
go

sp_msforeachdb @command1='use ; exec sp_find_col acc'

if not (object_id('dbo.sp_find_col') is NULL) 
begin 
    drop procedure dbo.sp_find_col -- 1  
end 
go

sample output:


master

%acc%
DB                             OWNER      Table_Name                               Col_Nm                         
------------------------------ ---------- ---------------------------------------- ------------------------------ 
master                         dbo        syslogins                                accdate
master                         dbo        syslogins                                hasaccess
master                         INFORMATIO ROUTINES                                 SQL_DATA_ACCESS

                                                                                                                                 
-------------------------------------------------------------------------------------------------------------------------------- 
model

%acc%
DB                             OWNER      Table_Name                               Col_Nm                         
------------------------------ ---------- ---------------------------------------- ------------------------------ 
master                         dbo        syslogins                                accdate
master                         dbo        syslogins                                hasaccess
master                         INFORMATIO ROUTINES                                 SQL_DATA_ACCESS

Notice the different databases from the db_name() function call from within the sp

I tried to prefix the information_schema call with a input variable, and had issues ... will look at that again (can you give me a pointer, perhaps?)

Thanx

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-01 : 04:00:45

maybe if you change this

FROM information_schema.Columns

to

FROM dbname().information_schema.Columns

Or, if you cannot use dbname in that way, you may have to use dynamic SQL.

-------
Moo. :)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-01 : 04:04:01

Will try ... best way to execute dynamic SQL is to use sp_executesql, I seem to recall?

(haven't developed too many SP's - usually been more of an overview/design man)

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-01 : 04:55:41

Yay - success! FYI - New version of code is:

--This script will create a stored procedure "sp_find_col" in the master database, so that the SP can be call from 
--each database on the instance. Working through the databases is done by a undocumented SP_MSFOREACHDB stored proc.
--
--Created By:	Regan Galbraith
--Created On:	2004-05-27
--Version:	1.1
--
--	changes for v1.1 - uses dynamic SQL to execute the sp, allowing it to receive the DB name as a parameter
--	and thus successfully parse through all databases
--
--USAGE: Find and replace (Ctrl+h) on the replacement "tag" below
--
--TAG:	email
--
-- Please NOTE not to save a replace version, since that can cause issues in re-use in future
use master 
go 
if not (object_id('dbo.sp_find_col') is NULL) 
begin 
    drop procedure dbo.sp_find_col -- 1  
end 
go
create procedure dbo.sp_find_col @col_nm sysname,@db_nm sysname = master
as
set @col_nm = '''%'+@col_nm+'%'''
declare @sql	nvarchar(512)
set @sql = 'SELECT left(table_catalog,30) as DB,left(table_schema,10) as OWNER,
		left(table_name,40) as Table_Name, left(column_name,30) as Col_Nm
		FROM '+@db_nm+'.information_schema.Columns where upper(column_name) like '+@col_nm
set nocount on
	select db_name()
--	print @col_nm	--here for debugging purposes
--	print @db_nm	--here for debugging purposes
--	print @sql	--here for debugging purposes
exec sp_executesql @sql
	if (@@rowcount = 0)
		Print 'Column "'+@col_nm+'" is NOT FOUND in database "'+@db_nm+'"'
go
set nocount on
go

sp_msforeachdb @command1='use ; exec sp_find_col email,?'

if not (object_id('dbo.sp_find_col') is NULL) 
begin 
    drop procedure dbo.sp_find_col -- 1  
end 
go

Thans for the help, Moo!!

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources