Querying sys.* tables from other databases
-
I'm tyring to write a utility procedure or two -- ones that will sit in the master database, and have names like sp_myProc. When in a user-defined database, I would run these to do things like list a table structure, list procedure code, and so forth.
A lot of what I want to do requires querying the myriad sys.* tables of SQL 2005 -- for example,
select *
from sys.objects
where name = @Name
When I run this locally, no problem, but when I run this as a master.dbo.sp_xxx procedure, it accesses the sys.* tables of the master database, not the current database. I can't reference the desired database's sys tables without dynamically building and executing the statements, and they complex fast.
The irritating thing is, Microsoft's procedures (such as sp_helpText, which uses something very like my example above) have no problem working this way. What's the trick? Is there a work-around?
Philip
P.S. And just why does Windows mess up the layout of text cut-n-pasted from SSMS, anyway?
-
I am assuming that you have created some procedures in the master database that you have named sp_xxxx. When you run those in a user database are you calling with master.dbo.sp_xxxx or just using sp_xxxx? If you have added the master.dbo. to the call, that is probably why it runs as if run in the master database. When you execute system stored procedure like sp_helpText in a user database, you normally don't precede it with master.dbo. as SQL Server automatically searches master first if the procedure name starts with sp_.
-
I've been doing the "shorthand", not specifying the (current) database name. (One reason I used the "sp_" naming convention...)
Philip
-
Could you post the DDL for one of the procedures? I'd like to give it a try and see what happens.
-
I've had the same problem and got an answer from an MVP saying that it's the intended behavior. I don't understand that as this breaks the functionality of sp_ stored procs in the master database (or at least severely cripples them). However, to answer your question, you can query sys.objects in, say, AdventureWorks database by referencing AdventureWorks.sys.objects.
-
I am going to assume that you are passing the DB name or are changing it. Because of many issues and usually the need for dynamic sql. This is the simplest method I have found.
Create temp objects for the tables that you want to reference and populate using execs
USE
MASTER
GO
DECLARE
@TheSQL NVARCHAR(MAX)
SELECT @TheSQL = 'select * from app.sys.objects'
SELECT *
INTO
#my_sys_objects
FROM sys.objects WHERE 1=0
INSERT INTO #my_sys_objects
EXEC sp_executesql @TheSQL
SELECT
* FROM #my_sys_objects
Have fun!
-
Same concept, but after reading again, this is closer to what I think you want.
USE
[master]
GO
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
PROCEDURE [sp_bob]
AS
SET
NOCOUNT ON
DECLARE
@theSQL NVARCHAR(MAX)
SELECT @theSQL = 'SELECT * FROM ' + DB_NAME() + '.sys.objects'
SELECT
*
INTO #sys_objects
FROM sys.objects WHERE 1 = 0
INSERT
INTO #sys_objects
EXEC sp_executesql @theSQL
-
Bob's pretty much got the issue right. As another example (and a useful utility when working with unfamiliar databases) here's what I've been working at.
Here's the original version. When run as a script it works fine, but as an sp_* procedure sitting in the master database it fails:
USE MASTER
IF objectproperty(object_id('sp_phkHelpText'), 'isProcedure') = 1
DROP PROCEDURE sp_phkHelpText
GO
/******************************************************************************
**
** Procedure: sp_phkHelpText
** Description: Quick utility to list the (text) definition of a database
** object (procedure, function, etc). If the text is not found [we shell
** out to sp_helpText], we pull out what we can.
**
**
** Return values: 0
**
** Input parameters: Name of object to check.
**
** Output parameters: none
**
** Rows returned: (Chunk of text)
**
*******************************************************************************
** Version History
*******************************************************************************
** Date: Author: Description:
** ---------- -------- -------------------------------------------
** 08/28/2007 PKelley Procedure created.
**
*****************************************************************************/
CREATE PROCEDURE dbo.sp_phkHelpText
@CheckThis sysname
-- Object to process
AS
SET NOCOUNT on
DECLARE @Command nvarchar(1000)
BEGIN TRY
-- Call the system procedure. If no text is found, it raises an error,
-- but that error gets eaten by the TRY/CATCH
EXECUTE sp_helpText @CheckThis
END TRY
BEGIN CATCH
-- No conventional text to list; first, check if it's a synonym; then check if
-- it's any know object; else print the "not found" message.
IF exists (select 1 from sys.synonyms where name = @CheckThis)
SELECT 'Synonym, references object ' + base_object_name Synonym
from sys.synonyms
where name = @CheckThis
ELSE IF exists (select 1 from sys.objects where name = @CheckThis)
SELECT 'No text found for "' + @CheckThis + '", object type ' + type + ' (' + type_desc + ')' NoText
from sys.objects
where name = @CheckThis
ELSE
PRINT 'Object "' + isnull(@CheckThis, '<null>' ) + '" not found'
END CATCH
RETURN
Cut and replace everything within the BEGIN - END CATCH with the following (it dynamically builds the relevant code) and it works:
-- No conventional text to list; first, check if it's a synonym; then check if
-- it's any know object; else print the "not found" message.
-- Note dynamic SQL, 'cause of the master-can't-reference-your-local-sys
-- tables shtick.
SET @Command = '
IF exists (select 1 from ' + db_name() + '.sys.synonyms where name = ''' + @CheckThis + ''')
SELECT ''Synonym, references object '' + base_object_name Synonym
from ' + db_name() + '.sys.synonyms
where name = ''' + @CheckThis + '''
ELSE IF exists (select 1 from ' + db_name() + '.sys.objects where name = ''' + @CheckThis + ''')
SELECT ''No text found for "' + @CheckThis + '", object type '' + type + '' ('' + type_desc + '')'' NoText
from ' + db_name() + '.sys.objects
where name = ''' + @CheckThis + '''
ELSE
PRINT ''Object "'' + isnull(''' + @CheckThis + ''', ''<null>'' ) + ''" not found'''
EXECUTE (@Command)
What I want is a format, syntax, what-have-you to access the system views without the need to build it all dynamically. Note the embedded call to sp_helpText--it has no problems doing this. (Maybe I should hack the system and flag my routines as system objects?)
This code is messy, and some of the things I'd like to do (with those sys.dm_* objects) are some complex I wouldn't bother trying to convert them to dynamically generated code. Any ideas?
Philip
P.S.
Just to mention, the idea for this utility is:
- Create master.dbo.sp_phkHelpText [phk are my initials, so I know who's to blame for dumping code in the master database]
- Configure a hot key in SSMS to call this process (Tools - Options - Environment - Keyboard, set ctrl+3 (or whichever) to "sp_phkHelpText" -- takes effect on all subsequently opened windows)
- In an SSMS query window, highlight your unknown object, hit crtl+3, and you get the code defining the procedure/function/trigger/view, the definition of the synonym, or a listing showing what kind of object it is--if any.
-
As far as I know that was possible in 2000 only.
For functions you needed additional steps. see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01l1.asp
In 2005 I don't think this is feasible and I am sure system tables can't be changed.
* Noel -
Now for the UNDOCUMMENTED:
USE master
exec sp_MS_marksystemobject 'YOURPROCEDURENAME'
USE [Your DB Name]
exec your proc
Enjoy!
* Noel -
Cool! New toys to play with tomorrow!
But years of pain force me to ask: is there an "unmark" procedure?
Philip
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply