March 13, 2023 at 12:00 am
Comments posted to this topic are about the item Find Invalid Objects in SQL Server
March 13, 2023 at 3:34 am
Link is going back to a topic written in 2012 which is an entirely different.
=======================================================================
March 13, 2023 at 9:13 am
I'd think it is better to really recompile all the procedures / triggers / views / functions etc. instead to hope, that the sys.dependencies-table is always 100% correct.
DECLARE @full_name sysname
DECLARE curModule CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT OBJECT_SCHEMA_NAME(sm.object_id) + '.' + OBJECT_NAME(sm.object_id)
FROM sys.sql_modules AS sm
ORDER BY 1
;
OPEN curModule;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM curModule INTO @full_name
IF @@fetch_status <> 0 BREAK;
PRINT 'Compiling ' + @full_name
EXEC sys.sp_recompile @objname = @full_name
END;
CLOSE curModule;
DEALLOCATE curModule;
will do the trick (feel free to check for the return code etc for handling invalid stuff).
PS: if you are using SELECT * in particularly in your views (something you should really avoid), you need to recompile the view always after changing the base tables (otherwise you could end with wrong content in the columns, e.g. if you dropped an unused column in the middle or changed a computed column (=DROP and ADD with new formula)
God is real, unless declared integer.
March 13, 2023 at 9:50 am
I'd think it is better to really recompile all the procedures / triggers / views / functions etc. instead to hope, that the sys.dependencies-table is always 100% correct.
DECLARE @full_name sysname
DECLARE curModule CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT OBJECT_SCHEMA_NAME(sm.object_id) + '.' + OBJECT_NAME(sm.object_id)
FROM sys.sql_modules AS sm
ORDER BY 1
;
OPEN curModule;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM curModule INTO @full_name
IF @@fetch_status <> 0 BREAK;
PRINT 'Compiling ' + @full_name
EXEC sys.sp_recompile @objname = @full_name
END;
CLOSE curModule;
DEALLOCATE curModule;will do the trick (feel free to check for the return code etc for handling invalid stuff).
PS: if you are using SELECT * in particularly in your views (something you should really avoid), you need to recompile the view always after changing the base tables (otherwise you could end with wrong content in the columns, e.g. if you dropped an unused column in the middle or changed a computed column (=DROP and ADD with new formula)
The big downside using te sp_recompile , is that you also invalidate cached plans ( of correct objects ), so they will need to be compiled and that may influence your ongoing applications.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 13, 2023 at 10:25 am
This is useful for me since it gives me a way of finding invalid objects without recompiling. I was using a script that recompiled the objects to find the broken dependencies mentioned previously. Your script does list some invalid objects which are not actually invalid however in triggers: Inserted, Deleted, and dbo.sp_send_dbmail. Also the first time I ran it failed with a PK error so you should consider removing that or making the PK column an identity? It needs more rigorous testing since it's identifying other objects which are not invalid; in my case I have a view created in schema "myschema" on a table in schema "dbo" that joins another view in schema "dbo" where your script says the joined/referenced dbo schema view is invalid.
March 13, 2023 at 10:49 am
This version also handles most cross database queries:
SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#invalid_db_objects') IS NOT NULL
BEGIN
DROP TABLE #invalid_db_objects;
END;
CREATE TABLE #invalid_db_objects
(DbName SYSNAME NOT NULL
, invalid_object_id INT
, invalid_obj_name NVARCHAR(1000)
, referenced_object NVARCHAR(1000) NOT NULL
, invalid_obj_type CHAR(2) NOT NULL
, Referenced_DbId INT
, referenced_database_name NVARCHAR(1000) NOT NULL
, referenced_server_name NVARCHAR(1000) NOT NULL
);
INSERT INTO #invalid_db_objects
SELECT DB_NAME() AS DbName
, RefSel.referencing_id
, QUOTENAME(SCHEMA_NAME(all_object.[schema_id]))
+ '.'
+ QUOTENAME(all_object.name) AS obj_name
, RefSel.referenced_object
, all_object.[type]
, RefSel.Referenced_DbId
, RefSel.referenced_database_name
, RefSel.referenced_server_name
FROM (
SELECT sed.referencing_id
, COALESCE(sed.referenced_schema_name
+ '.', '')
+ sed.referenced_entity_name as referenced_object
, DB_ID(ISNULL(referenced_database_name, DB_NAME())) AS Referenced_DbId
, COALESCE(referenced_server_name, CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')), CONVERT(SYSNAME, SERVERPROPERTY('ServerName'))) AS referenced_server_name
, ISNULL(referenced_database_name, DB_NAME()) AS referenced_database_name
FROM sys.sql_expression_dependencies AS sed
WHERE sed.is_ambiguous = 0
AND sed.referenced_id IS NULL
) AS RefSel
JOIN sys.objects AS all_object
ON RefSel.referencing_id = all_object.[object_id];
SELECT DbName
, invalid_object_id
, invalid_obj_name
, referenced_object
, invalid_obj_type
, Referenced_DbId
, referenced_database_name
, referenced_server_name
FROM #invalid_db_objects
WHERE OBJECT_ID(QUOTENAME(referenced_database_name) + '.' + referenced_object) IS NULL
ORDER BY DbName
, invalid_obj_name
, referenced_object;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 13, 2023 at 11:03 am
The primary key for #invalid_db_objects is on the invalid_object_id column, but if that object references multiple missing objects, then the insert will fail due to a duplicate key error. I've made the invalid_object_id column NOT NULL, taken the primary key definition out of the table creation statement, and created it using 2 columns as follows:
ALTER TABLE #invalid_db_objects ADD CONSTRAINT PK_invalid_db_objects_invalid_object_id_custom_error_message PRIMARY KEY CLUSTERED (invalid_object_id, custom_error_message)
This allows for such multiple references.
March 13, 2023 at 7:11 pm
@sql Simon, the #invalid_db_objects table does not have a column custom_error_message in it.
Sorry, yes, the original post contains a custom_error_message column. I was trying to incorporate the code in a comment.
March 13, 2023 at 10:30 pm
Very cool and useful. Thank-you for your efforts in writing this up.
I also ran into some issues with the Primary key, and changed it to
constraint pk_invalid primary key (invalid_object_id, custom_error_message) with (ignore_dup_key = on)
We also have triggers that make extensive use of the "deleted" and "inserted" virtual tables, so I excluded them from my results
SELECT invalid_obj_name [Invalid OBJECT NAME] , custom_error_message [Error Message], invalid_obj_type [Object Type] FROM #invalid_db_objects where custom_error_message not in ('Invalid object name ''inserted''', 'Invalid object name ''deleted''') March 14, 2023 at 1:18 am
@Toby Harman, I am not sure what meaning IGNORE_DUP_KEY would have for a PRIMARY key which must be unique. See also, https://stackoverflow.com/a/2594283/447901
March 14, 2023 at 11:59 pm
It allows you to enter duplicate values and then drops them semi-silently.
March 17, 2023 at 6:18 am
This was removed by the editor as SPAM
March 17, 2023 at 4:20 pm
I would switch the temp table to be
CREATE TABLE #invalid_db_objects
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
invalid_object_id INT NOT NULL,
invalid_obj_name NVARCHAR(1000) NOT NULL,
custom_error_message NVARCHAR(3000) NOT NULL,
invalid_obj_type CHAR(2) NOT NULL
);
In case an object has more than one error, as mine did.
Ozzie
March 19, 2023 at 1:31 am
Beyond what others have stated, this will have many false positives. Triggers that reference INSERTED and DELETED will show as invalid. Excluding these is pretty simple. However, update and delete statements referencing an alias will show as being invalid. In the example below, t1 will show as being invalid.
UPDATE t1
SET Col1 = t2.value1
FROM dbo.table1 t1
JOIN dbo.table2 t2 ON t1.id = t2.id
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply