listing objets in any DB on server

[womalley]

Hello,
I posted this over at sqlserverentral.com but have not had any reply's.
If you are a member here is the link:
http://sqlservercentral.com/scripts/...?scriptid=1620

If you are not member here is the code:

CREATE PROCEDURE spFindObjectUsage
 @ObjectToFind    NVARCHAR(100) = ''
,@ResultMessage    VARCHAR(200) = '' OUTPUT
AS
DECLARE
     @ReturnCode        INT
    ,@StringToExecute    NVARCHAR(1500)
    ,@DBToProcess        INT
    ,@ServerName        VARCHAR(200)
    ,@TableWithServer    VARCHAR(200)
    ,@NameOfDatabase    VARCHAR(50)
--
DECLARE    @ServerDatabaseTables TABLE
(TempTblID    INT NOT NULL IDENTITY(1,1)
,DBName        VARCHAR(100) NOT NULL DEFAULT ''
,Processed    BIT NOT NULL DEFAULT 0)
--
CREATE TABLE #ServerDatabaseObjectUsage 
(UsageID        INT NOT NULL IDENTITY(1,1)
,DBName            VARCHAR(100) NOT NULL DEFAULT ''
,ObjectUsedIn        VARCHAR(200) NOT NULL DEFAULT ''
,TypeOfObject        VARCHAR(50)  NOT NULL DEFAULT ''
,IsColumnOfTable     BIT NOT NULL DEFAULT 0)
--
-- first get all the databases on the current server
--
INSERT INTO @ServerDatabaseTables
    (DBName)
SELECT
    name
FROM master.dbo.sysdatabases
--
SET @TableWithServer = ''
SET @NameOfDatabase  = ''
--
SET NOCOUNT ON
-- each database has it's own listing of System Objects so inorder to get
-- a correct listing we will need to go through every database.
-- the only way I know to do this is using sqlexec.
-- I know it is not the best way but we will need the ability to dynamically
-- tell the query what system tables to use. Ex: master.dbo.systemobjects or production.dbo.systemobjects...ect
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN
    SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)
    --
    SELECT   @ServerName       = DBName + '.dbo.'
        ,@NameOfDatabase  = DBName
    FROM @ServerDatabaseTables
    WHERE TempTblID = @DBToProcess
    --
    SET @StringToExecute =  'INSERT INTO #ServerDatabaseObjectUsage ' +
                    '(DBName' +
                ',ObjectUsedIn' +
                ',TypeOfObject' + 
                ',IsColumnOfTable) ' +
                'SELECT DISTINCT ' +
                char(39)+@NameOfDatabase+char(39)+ 
                ',obj.NAME' + 
                ',(CASE obj.XTYPE  WHEN ' + char(39) + 'P' + char(39) + 
                         ' THEN ' + char(39) + 'PROCEDURE' + char(39) +
                         ' WHEN ' + char(39) + 'V' + char(39) + 
                         ' THEN ' + char(39) + 'VIEW' + char(39) +
                         ' WHEN ' + char(39) + 'U' + char(39) + 
                         ' THEN ' + char(39) + 'USER TABLE' + char(39) +
                         ' WHEN ' + char(39) + 'D' + char(39) + 
                         ' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
                         ' WHEN ' + char(39) + 'F' + char(39) + 
                         ' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
                         ' WHEN ' + char(39) + 'IF' + char(39) + 
                         ' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
                         ' WHEN ' + char(39) + 'FN' + char(39) + 
                         ' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
                         ' WHEN ' + char(39) + 'TF' + char(39) + 
                         ' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
                         ' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
                ',(CASE WHEN (SELECT count(*) FROM '+@ServerName+'syscolumns where name='+char(39)+@ObjectToFind+char(39) + ') > 0 ' +
                    'THEN 1 ELSE 0 END)' + 
                'FROM ' + @ServerName + 'sysobjects as obj ' + 
                'LEFT JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' + 
                'LEFT JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' + 
                'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + 
                ' OR syscol.name LIKE '  + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + 
                ' ORDER BY obj.name '
    SET @StringToExecute = LTRIM(RTRIM(@StringToExecute))
    PRINT LEN(@StringToExecute)
    --
    exec sp_executesql @StringToExecute
    --
    IF (@@ERROR != 0) BEGIN
        SET @ReturnCode = 1
        GOTO END_PROCEDURE
    END
    --
    UPDATE @ServerDatabaseTables
    SET Processed = 1
    WHERE TempTblID = @DBToProcess
END
--
SELECT * FROM #ServerDatabaseObjectUsage
--
DROP TABLE #ServerDatabaseObjectUsage
--
SET @ReturnCode = 0

END_PROCEDURE:
    IF (@ReturnCode != 0) BEGIN
        SET @ResultMessage = 'A NON ZERO Return code has occured, Please investigate this problem ' + CAST(@ReturnCode AS VARCHAR(2))
    END ELSE BEGIN
        SET @ResultMessage = 'OK'
    END
RETURN @ReturnCode 

I am looking for feed back on the code:
If you like it?
Is there a better way to do this?
And any other comments or suggestions are welome.

Please let me know,

William O'Malley

[Krzemo]

First of all - why don't U use cursors instead of that nasty update/select WHILE?

Why U limit output only to "dbo" schema? U can do that by cutting of "dbo" from select statement, so for example instead of
"SELECT * FROM Norhwind.dbo.MyTable" U can allways use "SELECT * FROM Norhwind..MyTable"
Of course sys.. tables are ususally in "dbo" schema but ... not allways ;-)

"GOTO END_PROCEDURE" - brrr!
U should call RAISERROR and leave procedure using RETURN statement.

IMHO your code for finding dependencies is very ugly. U make unnecesery joins to few (usually large) tables just to use DISTINCT to cut it off. Instead of that U can use "EXISTS" or "IN (SELECT..) " keywords
Main loop IMHO should look like this:

DECLARE @Name VARCHAR(255)
DECLARE crsr CURSOR LOCAL FAST_FORWARD
FOR SELECT name FROM master..sysdatabases

OPEN crsr 
FETCH NEXT FROM crsr INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
SET @StringToExecute = 'INSERT INTO ...SELECT ... FROM '+@Name+'..sysobjects'
..... your code here!
FETCH NEXT FROM crsr INTO @Name
END
CLOSE crsr
DEALLOCATE crsr

Best regards,
Krzemo.

[womalley]

First of all - why don't U use cursors instead of that nasty update/select WHILE?

Best regards,
Krzemo.

Cursors? ARE U SERIOUS?

First they are slower and second....Aah..never mind

thanks for the ...... suggestions

[Krzemo]

Cursors? ARE U SERIOUS?

First they are slower and second....

Slower than UPDATE/ SELECT MIN()/ in the loop WHILE EXISTS(SELECT) ?
Really ?

thanks for the ...... suggestions

I'm glad that I .... helped.

And any other comments or suggestions are welome.

Hope that it is true

[womalley]

Maybe if U had made YOUR suggestions more constructive and less critical then I would have received them better.

And yes cursors are slow...thats a fact.

I have an idea...rather then US act like a bunch of babies trying to see who can cry louder..post some code, make some mods MAKE THE CODE better!

Wow what an Idea...phew...that hurt..

All I wanted was some feed back a little developer to developer talk and maybe just maybe some code posted back with a "Hay nice try but check this out"

Oh well so much for the developer community being about helping eachother out...

EDIT--->
Awsome Article:
SQL Server Performance Tuning for SQL Server Developers


Thanks again,
Will

[Krzemo]

And yes cursors are slow...thats a fact.

Lets see....

Testing SQL Server 2000 SP3 on Windows XP Prof.
Exaple 1 - cursor

 
DECLARE @Name VARCHAR(255),@i INT
SET @i=1
WHILE @i<100000
BEGIN
DECLARE crsr CURSOR LOCAL FAST_FORWARD
FOR SELECT name FROM master..sysdatabases
OPEN crsr 
FETCH NEXT FROM crsr INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM crsr INTO @Name
END
CLOSE crsr
DEALLOCATE crsr
SET @i=@i+1
END

Execution time 0:00:25

Example 2 -"faster" solution

 
DECLARE 
	 @ReturnCode		INT 
	,@StringToExecute	NVARCHAR(1500) 
	,@DBToProcess		INT 
	,@ServerName		VARCHAR(200) 
	,@TableWithServer	VARCHAR(200) 
	,@NameOfDatabase	VARCHAR(50)
,@i INT
SET @i=1
WHILE @i<1000 -- I set it 100 times lower than exaple 1 because I havn't so much time to waste;-)
BEGIN 
DECLARE	@ServerDatabaseTables TABLE 
(TempTblID	INT NOT NULL IDENTITY(1,1) 
,DBName		VARCHAR(100) NOT NULL DEFAULT '' 
,Processed	BIT NOT NULL DEFAULT 0) 
INSERT INTO @ServerDatabaseTables 
	(DBName) 
SELECT 
	name 
FROM master.dbo.sysdatabases 
SET @TableWithServer = '' 
SET @NameOfDatabase  = '' 
SET NOCOUNT ON 
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN 
	SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0) 
	SELECT   @NameOfDatabase  = DBName 
	FROM @ServerDatabaseTables 
	WHERE TempTblID = @DBToProcess 
	UPDATE @ServerDatabaseTables 
	SET Processed = 1 
	WHERE TempTblID = @DBToProcess 
END 
SET @i=@i+1
END

Execution time 0:00:43 (to compare U need to multiply by 100 because of 100 times shorter loop)

Maybe if U had made YOUR suggestions more constructive and less critical then I would have received them better.

My suggestion IMHO was constructive since I pointed it out what and how should be changed (with example). Rewriting YOUR code is YOUR job since YOU are the author of that script. And U have been posted it to some places making other believe that what U have done is a good programmer practice. Maybe it wasn't too nice to drop some cold water right to your head but it was stronger than me and I couldn't keep from it. I just see too many bad style programming over and over.

And few words about squizing max of performance. Sometimes it is better to have slower nice code than super fast ugly and hard to maintain code. Especialy when U operate on small tables and/or use script not often. U are a kind of programmer which tends to make his program a bunch of tricks. That is like shooting to small birds with cannon. And since your trick code is much much slower than nice cursor solution than there is no excause for using it.

All I wanted was some feed back a little developer to developer talk and maybe just maybe some code posted back with a "Hay nice try ...."

So U shouldn't ask for any comments - U should ask for praises

And about article ...
I have impression that U didn't red it carefully enough...
Your solution is processing databases one by one (row by row) - it is ideal place for LOCAL FAST_FORWARD cursor.
I trully agree with article that if U can write 1 select that do it all than it will be the best ultimate solution - but U can't - can U ?
And U should make no asumption about performance until U test it yourself, since there is allways posibility that some trick is no longer valid or U just make wrong assumption.

Krzemo.

[ITGURU]

Dear womalley,

Below is your modified code which is almost 10 times faster than existing one. In your code i have made small changes (find changes under comment start with Modified by Guru on 7 Feb 2006 Start and End with Modified by Guru on 7 Feb 2006 End):

If you find it perfect please rate this reply.

CREATE PROCEDURE spFindObjectUsage
@ObjectToFind NVARCHAR(100) = ''
,@ResultMessage VARCHAR(200) = '' OUTPUT
AS
DECLARE
@ReturnCode INT
,@StringToExecute NVARCHAR(1500)
,@DBToProcess INT
,@ServerName VARCHAR(200)
,@TableWithServer VARCHAR(200)
,@NameOfDatabase VARCHAR(50)

--
DECLARE @ServerDatabaseTables TABLE
-- Modified by Guru on 7 Feb 2006 Start
--(TempTblID INT NOT NULL IDENTITY(1,1)
(TempTblID INT NOT NULL IDENTITY(1,1) primary key
-- Modified by Guru on 7 Feb 2006 End
,DBName VARCHAR(100) NOT NULL DEFAULT ''
,Processed BIT NOT NULL DEFAULT 0)

--
CREATE TABLE #ServerDatabaseObjectUsage
(UsageID INT NOT NULL IDENTITY(1,1)
,DBName VARCHAR(100) NOT NULL DEFAULT ''
,ObjectUsedIn VARCHAR(200) NOT NULL DEFAULT ''
,TypeOfObject VARCHAR(50) NOT NULL DEFAULT ''
,IsColumnOfTable BIT NOT NULL DEFAULT 0)
--
-- first get all the databases on the current server
--
INSERT INTO @ServerDatabaseTables
(DBName)
SELECT
name
FROM master.dbo.sysdatabases
--
SET @TableWithServer = ''
SET @NameOfDatabase = ''
--
SET NOCOUNT ON
-- each database has it's own listing of System Objects so inorder to get
-- a correct listing we will need to go through every database.
-- the only way I know to do this is using sqlexec.
-- I know it is not the best way but we will need the ability to dynamically
-- tell the query what system tables to use. Ex: master.dbo.systemobjects or production.dbo.systemobjects...ect
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN
SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)
--
SELECT @ServerName = DBName + '.dbo.'
,@NameOfDatabase = DBName
FROM @ServerDatabaseTables
WHERE TempTblID = @DBToProcess
--
-- Modified by Guru on 7 Feb 2006 Start
-- SET @StringToExecute = 'INSERT INTO #ServerDatabaseObjectUsage ' +
-- '(DBName' +
-- ',ObjectUsedIn' +
-- ',TypeOfObject' +
-- ',IsColumnOfTable) ' +
-- 'SELECT DISTINCT ' +
-- char(39)+@NameOfDatabase+char(39)+
-- ',obj.NAME' +
-- ',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) +
-- ' THEN ' + char(39) + 'PROCEDURE' + char(39) +
-- ' WHEN ' + char(39) + 'V' + char(39) +
-- ' THEN ' + char(39) + 'VIEW' + char(39) +
-- ' WHEN ' + char(39) + 'U' + char(39) +
-- ' THEN ' + char(39) + 'USER TABLE' + char(39) +
-- ' WHEN ' + char(39) + 'D' + char(39) +
-- ' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
-- ' WHEN ' + char(39) + 'F' + char(39) +
-- ' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
-- ' WHEN ' + char(39) + 'IF' + char(39) +
-- ' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
-- ' WHEN ' + char(39) + 'FN' + char(39) +
-- ' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
-- ' WHEN ' + char(39) + 'TF' + char(39) +
-- ' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
-- ' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
-- ',(CASE WHEN (SELECT count(*) FROM '+@ServerName+'syscolumns where name='+char(39)+@ObjectToFind+char(39) + ') > 0 ' +
-- 'THEN 1 ELSE 0 END)' +
-- 'FROM ' + @ServerName + 'sysobjects as obj ' +
-- 'LEFT JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' +
-- 'LEFT JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' +
-- 'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) +
-- ' OR syscol.name LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) +
-- ' ORDER BY obj.name '
SET @StringToExecute = 'INSERT INTO #ServerDatabaseObjectUsage ' +
'(DBName' +
',ObjectUsedIn' +
',TypeOfObject' +
',IsColumnOfTable) ' +
'SELECT DISTINCT ' +
char(39)+@NameOfDatabase+char(39)+
',obj.NAME' +
',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) +
' THEN ' + char(39) + 'PROCEDURE' + char(39) +
' WHEN ' + char(39) + 'V' + char(39) +
' THEN ' + char(39) + 'VIEW' + char(39) +
' WHEN ' + char(39) + 'U' + char(39) +
' THEN ' + char(39) + 'USER TABLE' + char(39) +
' WHEN ' + char(39) + 'D' + char(39) +
' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
' WHEN ' + char(39) + 'F' + char(39) +
' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
' WHEN ' + char(39) + 'IF' + char(39) +
' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'FN' + char(39) +
' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'TF' + char(39) +
' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
',0 ' +
'FROM ' + @ServerName + 'sysobjects as obj ' +
'INNER JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' +
'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39)

SET @StringToExecute = @StringToExecute + ' UNION ALL '

SET @StringToExecute = @StringToExecute + ' SELECT DISTINCT ' +
char(39)+@NameOfDatabase+char(39)+
',obj.NAME' +
',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) +
' THEN ' + char(39) + 'PROCEDURE' + char(39) +
' WHEN ' + char(39) + 'V' + char(39) +
' THEN ' + char(39) + 'VIEW' + char(39) +
' WHEN ' + char(39) + 'U' + char(39) +
' THEN ' + char(39) + 'USER TABLE' + char(39) +
' WHEN ' + char(39) + 'D' + char(39) +
' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
' WHEN ' + char(39) + 'F' + char(39) +
' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
' WHEN ' + char(39) + 'IF' + char(39) +
' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'FN' + char(39) +
' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'TF' + char(39) +
' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
',(CASE WHEN (syscol.NAME='+char(39)+@ObjectToFind+char(39) + ') THEN 1 ELSE 0 END)' +
'FROM ' + @ServerName + 'sysobjects as obj ' +
'INNER JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' +
'WHERE syscol.name LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) +
' ORDER BY obj.name '
-- Modified by Guru on 7 Feb 2006 End

SET @StringToExecute = LTRIM(RTRIM(@StringToExecute))
PRINT LEN(@StringToExecute)
--
exec sp_executesql @StringToExecute
--
IF (@@ERROR != 0) BEGIN
SET @ReturnCode = 1
GOTO END_PROCEDURE
END
--
UPDATE @ServerDatabaseTables
SET Processed = 1
WHERE TempTblID = @DBToProcess
END
--
SELECT * FROM #ServerDatabaseObjectUsage
--
DROP TABLE #ServerDatabaseObjectUsage
--
SET @ReturnCode = 0

END_PROCEDURE:
IF (@ReturnCode != 0) BEGIN
SET @ResultMessage = 'A NON ZERO Return code has occured, Please investigate this problem ' + CAST(@ReturnCode AS VARCHAR(2))
END ELSE BEGIN
SET @ResultMessage = 'OK'
END
RETURN @ReturnCode

If you need any help further in this regard feel free to contact me.

[womalley]

Very nice mod!

1) I really like that you added the Primary Key to the Temp Table
I did append to that and add NONCLUSTERED otherwise it will create a clustered key and why have a unique key be clustered... =)

2) I like that you got rid of the 2 left joins
Creating to different selects using inner joins which reduced the result set being returned

Awsome replay and great fixes that I will be sure to include.
This routine use to take upwards of 1.5 minutes now it returns results in seconds!

Thanks,
Will

[ITGURU]

Dear womalley,

Please rate my suggestion and also send me a copy if you made any more changes in the script.

[womalley]

ITGuru,
I rated your post after I read it.

When I click on it now I get:

You cannot give Reputation to the same post twice.

I will copy you...

Thank you again,
Will

[ITGURU]

Dear womalley,

Thanks for giving Rating for my suggestion and sorry for repeatedly asked for this actually I have not seen that you already given the rating for my posting.

Could you please tell me how can I see the Rating given by user for my posting without going to User Control Panel Page.

[cherish]

Hello ITGURU,

You can see the Rating given to you by a user by clicking "Rate This Post" on the post you want to check. But it won't show you exactly how many points you received, nor will it show who rated you on that specific post. You will only find how your post fared like "Somewhat Positive", "Very Positive", etc.

For more information as to how these ratings work, etc.. you might to check the Feedback forum. This has been asked countless times.

Regards,
Cherish

[womalley]

Thank you Cherish


Will

[cherish]

Thank you Cherish


Will

You're welcome. And sorry for butting in like that... I just can't help but post a reply after reading ITGURU's post.

[exterminator]

Krezmo... what you have shown is not a way to compare the efficiency of a block of code implemented in two different ways.

My objection is the fact that both of codes do not do exactly the same thing. One is just opening a cursor and looping in some 'n' number of time while the other is even updating records.

You can only compare those when the things achieved by them meet the exact same objective!

Having said that, there is no denying the fact that Cursors are slow. But sometimes become a necessity.. However, with proper use of joins and other SQL constructs you can usually avoid them. Regards.

//I am feeling too lazy to have a look at OP's code - may be tomorrow I will.