SP and database - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SP and database

Author

Topic

madhan
Yak Posting Veteran

59 Posts

Posted - 2014-10-20 : 15:17:12

I am trying to find a Stored procedure in database that name is Gun_XXX. I can't filter it out using SP-Filter settings.
So I write up the following query to find out is the SP avaliable?

SELECT name, type
FROM dbo.sysobjects
WHERE (type = 'P') and name='GUN_XXX'

It is avilable in database. How would I find out what database is the SP available and
how to get this Stored Procedure schema name? Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-20 : 15:24:22

[code]
EXEC sp_MSforeachdb N'Use ?;
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE type = ''P'' and name=''GUN_XXX'')
PRINT ''?''';

[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

madhan
Yak Posting Veteran

59 Posts

Posted - 2014-10-20 : 15:36:22

quote:
Originally posted by tkizer

EXEC sp_MSforeachdb N'Use ?;
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE type = ''P'' and name=''GUN_XXX'')
PRINT ''?''';

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Thanks for the reply. I tried executing your code as


DECLARE	@result AS TABLE
(
	database_name	sysname,
	type1		sysname
);
	
	insert into @result 
	EXEC sp_MSforeachdb N'Use ?; 
			IF EXISTS (SELECT * FROM dbo.sysobjects WHERE type = ''P'' and name=''GUN_XXX'')
				PRINT ''?''';

and received 0 rows affected. Why?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-20 : 15:39:23

It isn't going to return a result set that you can put into your table variable.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

madhan
Yak Posting Veteran

59 Posts

Posted - 2014-10-20 : 15:58:23

quote:
Originally posted by tkizer

It isn't going to return a result set that you can put into your table variable.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ok, could you please let me know how I can view the result in this case?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-20 : 16:00:26

If it finds it, it'll print out the database name. If you see no results, then that stored procedure name doesn't exist in any of the databases.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-20 : 16:01:22

declare a temp table (not table variable).

change PRINT ''?''' to

insert into <yourtemptable> select <columns you want>

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-10-20 : 21:54:40

plz try


SELECT name, type 
FROM dbo.sysobjects
WHERE (type = 'P') and name LIKE 'GUN%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources