March 26, 2015 at 10:52 am
I am querying this table to drop the Stored procedures in the database upon upgrade.
DECLARE @TheName Varchar (255),@SQL Varchar(2000)
DECLARE Sproc_Drop INSENSITIVE CURSOR FOR
SELECT *
FROM [sys].[procedures]
ORDER BY 1
OPEN Sproc_Drop
FETCH NEXT FROM Sproc_Drop INTO @TheName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL ='
DROP PROCEDURE [dbo].['+ @TheName+']
'
EXECUTE (@SQL)
FETCH NEXT FROM Sproc_Drop INTO @TheName
END
CLOSE Sproc_Drop
DEALLOCATE Sproc_Drop
There is a slight potential a client could create there own sprocs in the DB and I do not want to blow them away. Outside of keeping a list of all the sprocs we have in every version and querying that table, or going by naming convention is there a way to narrow down the selection? Could I put a signature on the sproc saying this is a 'JKSQL' sproc
March 26, 2015 at 11:19 am
In effect, yes, you could add a "signature" using extended properties. It could be anything you want, just an entry to show that you created the proc.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 26, 2015 at 11:43 am
I was thinking of something like this. It would work and I could script it so it would at least start off automated. I could poke holes in it since the existing deprecated sprocs that we currently drop would not have extended properties. I think the question I am asking is to tough to answer. There are several solutions not automatic. I think we will need to keep with our static list of sprocs to drop to avoid other potential issues. That or a table with the version and checkbox if it is obsolete.
Thanks for the time and the idea
March 26, 2015 at 1:54 pm
Why do you need to drop procedures before upgrade?
Why do you need to drop ALL procedures before upgrade?
I strongly suggest you to rethink your upgrade strategy.
March 26, 2015 at 2:34 pm
In our upgrade process we use table types and tweak sprocs constantly. We found this to be the easiest way to maintain the changes. it is not the best for the compiled ones that did not change, but seems to work currently. We have three solutions:
1)Keep the current process where we have static drop statements. Deprecate Sprocs by putting the version it was deprecated in. Later we clean out those sprocs when we no longer have to worry about upgrades. File will grow and grow.
2)Use MS extended properties to document a sproc. Have an identifier in the value that we could query to know that it is our sproc. Therefore we can drop it without worry. This would be implemented, but it would be about two years before we could get rid of the drop sprocs file.
3)Make a table with all of our sprocs in it. It will have 4 fields (Schema, Name, Version implemented, Obsolete). I could then query this table on upgrade to know what to drop. This would be maintained when adding or deprecating sprocs.
4) Change the existing dbo schema of the sprocs to something else so we can filter on schema. Then drop with better certainty
i think we will do 4 with searching the DAC and changing the sprocs from dbo to the schema we choose.
March 26, 2015 at 2:57 pm
That's a very viable solution, but keep in mind that you can get potential security issues if you do reference any 'dbo.' objects from a different schema you create.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply