How to interprete this script?
-
It is not an easy question, at least to me. Any input will be welcome and greatly appreciated.
The following script is used for tracing any change in the system stored procedures in the master database. All scripts run under master database.
1. CREATE a table for saving the information after installation.
CREATE TABLE tblSysSP (
[ID] INT NOT NULL,
[name] VARCHAR(27) NOT NULL,
[number] SMALLINT NOT NULL,
[colid] SMALL INT NOT NULL,
[text] NVARCHAR (4000) NOT NULL
) ON PRIMARY
GO
2. Pull data from master database into the table created above
INSERT INTO tblSysSP
SELECT so.id, LEFT(so.name, 27), sc.number, sc.colid, sc.text
FROM sysobjects so INNER JOIN syscomments sc
ON so.id = sc.id
WHERE xtype IN ('P', 'X') AND number > 0
3. Run the following script to list all system stored procedures, which have been changed since loading data onto the table, tblSysSP
SELECT DISTINCT t.name
FROM tblSysSP t LEFT JOIN syscomments sc
ON t.id = sc.id
AND t.number = sc.number
AND t.colid = sc.colid
AND t.text = sc.text
WHERE sc.id IS NULL
My question is, the table, syscomments, does not have any id in null. How does the WHERE clause retrieve the desired information?
-
syscomments is also used to store long stored proc when they overflow the field for the script's text. I suspect that those with id null are the records related to SP's. In your cases none of your SP's are long enough to overflow to syscomments.
To test, get a long script make it a SP and run the script above again.
-
Thank you so much for your input.
I have to mention that the script snippets provide our DBA team excellent data. There is no error at all. I just need a better interpretation on these scripts.
-
Step back from the problem and look at the result of the "JOIN" clause. A left join will retrieve all rows from the tblSysSp table, and for each row it retrieves if it does not locate a matching record in the syscomments table it will poulate the result sets (the internal temporary composit table) with NULLS for each column of the syscomments table.
So now the internal "composit" table of the join has a column for EVERY column in both tables.
When a stored procedure is changed the syscomments.text changes, however your tblSysSP table still has the "OLD" text so JOIN now fails to find the record in syscomments (sc.text = t.text) so all the columns from syscomments that are included in the result set are NULL.
The WHERE clause you see in the original SQL is being applied against the "Result"/"Internal Temporary Composit" table, so all records where sc.id is null (or ANY column from the syscomments table) indicates that the procedure has changed. The select portion of the SQL then returns the name for the SP from your table. Thus you identify any SP that changed (though you will not identify any new SP's though it will get deleted ones).
Hope that helps and that any non-standar terminolgy I used didn't confuse the issue.
James.
-
Thank you so much, James. Your interpretation is right and clear.
-
No all get the ID value for the related SP, they have no other relationship otherwise.
-
Why are you monitoring for changes in 'system' stored procedures in the master database ? Do users have the 'sa' password or 'sysadmin' role ? or is this a compliance issue.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply