Where can I create a Ms Sql store procedure for best access

Thanx, great info!

Just figured it out, if I create the the store procedure with "sp_", that is "sp_vt", then I can run it from any where.  But if I created without "sp_", that is "vt", then I cannot run it from any database.

I want to get into a database in my sql studio, and just run "exec vt" and have my tables listed.  

How can I have my store procedure "vt" run from any database without creating it in every DB and within having the "sp_" in fron of the "vt"?

Best not to use prefix SP - that is what MS uses for system type stuff.

If all in same instance, can always specify the db e.g. yourDB.dbo.usp_vt ....

If I create the sp without the "sp_" can't be run from anywhere.

How do you mean?

Not quite. SP refers to System Stored Procedures which appear in every database.

Your naming convention does not automatically create a system stored procedure.

In any case, your SP would be using the db and schema where it was created.

For example, if I create a DB called EE, then create a stored procedure called SP_THIS_IS_ONLY_A TEST...
Then I create a 2nd DB called AnotherDB and try to run SP_THIS_IS_ONLY_A TEST then you will get an error - cannot find it.
But if I specify EE.dbo.SP_THIS_IS_ONLY_A TEST from AnotherDB it will execute.

Now the gotcha in that is the SP only knows about objects in its database (in this case EE). So getting anything from Information Schemas will not reflect the database from where it was called, but from where it lives.

Because system stored procedures are automatically generated when you create a DB, it gives the impression that they must be available globally, but the reality is they reside locally in the sys schema of the DB you happen to be in.

In SSMS go into any DB by double clicking on it. Locate 'Programmability' and expand that. Then expand 'Stored Procedures', then expand 'System Stored Procedures' and there they are, available locally in the sys schema of the DB you are in.

To get info such as INFORMATION_SCHEMA.TABLES, you need to be "in" that DB. Thats why we need to 'USE dbname' which is not permitted within a user-defined stored procedure (regardless of how we name it).

It does sound complex, and it can be very frustrating. Thats how and why we discover thins like sp_MSforeachdb  :)

Happy New Year - we are already 2018 'down-under'.

Happy New Year 2018! Thanx!

Let me see if I can transmit my issue:

- I created 2 stored procedures in Master database  (sp_vt for View Tables, and sp_vc for View Columns)
- I first created these sp without including  "sp_", that is vt and vc
- when goung to WORKAREAS and running "exec vt", it said "Could not find stored procedure".
- Then I re-created these 2 procedures with "sp_' at the begining (sp_vt and sp_vc)
- with this naming i noticed that when I go into WORKAREAS and run exec sp_vt , it displays the tables.

Notice the pix below, my store procedures are only in Master, not in WORKAREAS and they are found when running them within WORKAREAS.

 

What am i trying to accomplish:

- whenever I go into a database, run a small line, smallest as possible and display the tables of said database.
  (i wanted to use exec vt not exec sp_vt, but it I cannot run it outside Master db if the store procedure is vt)
- I want to run my 2 store procedures from within any database.

Questions:
- why with "sp_" I can run it from anywhere and without "sp_" i can only use it in Master?
- How can I create a stored procedure and run it from within any database with as low character as possible; that is, exec vt without "sp_"?

Sorry rayluvs, I said I would get back to you, and never did....

Now that the question is closed, I assume that it is all OK - but feel free to seek more explanation.

If you end up creating a SP in master, then make very sure you have a unique naming convention. I have (reluctantly) used SP_NOTMS_ .... as a prefix before, but the real gotcha is getting context as to where it is being called from.

So, while it is possible to code in MASTER

if object_id('SP_NOTMS_CHECK_INFO_SCHEMA','P') is not null drop proc SP_NOTMS_CHECK_INFO_SCHEMA;
GO
create proc SP_NOTMS_CHECK_INFO_SCHEMA @DBNAME varchar(100) = null, @OBJECT varchar(100) = 'Tables'
as
declare @d varchar(100) = (select iif(isnull(@DBNAME,'')>'',@DBNAME,'.')) 
declare @s varchar(100) = (select upper(iif(@OBJECT in ('tables','columns','views','routines','domains'),@OBJECT,'TABLES'))) 
exec('select * from '+@d+'.INFORMATION_SCHEMA.'+@s)
GO

Select allOpen in new window

 lets say I also have DB1, DB2, DB3. So to execute all I minimally have to do is exec sp_notms_check_info_schema from any of DB1,DB2 or DB3

But as with some MS supplied routines, I sometimes have to include the DB where the routine lives. A classic is EXEC master..xp_cmdshell

And if I was to use any prefix other than SP, i would also need to include the db name where that procedure resides. For example, to rename my SP to USP_NOTMS_CHECK_INFO_SCHEMA even in Master, I would then need to include the db. Ie  EXEC master..USP_NOTMS_CHECK_INFO_SCHEMA

And if I am going to avoid clashes with MS procedures (especially those secret ones) via a naming convention, then I may as well take the extra time to create the routine 'properly' to future proof.

So, why not create in DB1 ? To use it from any on the other DB's then all I have to do is reference it properly ie EXEC DB1..USP_NOTMS_CHECK_INFO_SCHEMA

And avoid any future problems with running over / into / accidentally corrupting an SP

I have even seen a site where all procs and functions where located very deliberately (and securely) separately and forced correct referencing.

Does that make sense ?

Thank you very much! Great Info!

Happy to have been able to help :)