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

We want to run a store procedure (that displays tables of current database) without creating the SP in every DB.  How can we do this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
well, you dont need a stored procedure. There is a system view. Or to look at all DB there is a SP already.

or to just see the current DB tables,

Open in new window

You can use an undocumented stored procedure sp_msforeachdb and then get information from INFORMATION_SCHEMA from other DB's
exec sp_msforeachdb 'use [?]; 
-- or column names
exec sp_msforeachdb 'use [?]; 

Open in new window

The ? gets replaced by the DBNAME
rayluvsAuthor Commented:
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"?
Mark WillsTopic AdvisorCommented:
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 ....
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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

How do you mean?
Mark WillsTopic AdvisorCommented:
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'.
rayluvsAuthor Commented:
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.

- 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_"?
Mark WillsTopic AdvisorCommented:
well, SP is a very special prefix for a stored procedure. When you create a DB, it doesnt actually copy the individual system stored procedures, really it is like a pointer to Master.

There is more to it, and will get back to you later.... In the meantime read : https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
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;
create proc SP_NOTMS_CHECK_INFO_SCHEMA @DBNAME varchar(100) = null, @OBJECT varchar(100) = 'Tables'
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)

Open 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 ?
rayluvsAuthor Commented:
Thank you very much! Great Info!
Mark WillsTopic AdvisorCommented:
Happy to have been able to help :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.