This is my second SQL Tidbit and I have already decided to change the name to SQL Bacon Bits. Why? Because it’s my blog and I can. These quick posts are supposed to be simple and yummy, just like bacon.
Today’s featured product is ER/Studio Data Architect.
The Need
Whenever a change script is created from an ER/Studio Data Architect model, the original objects are kept with a date time stamp as part of their name. These original copies are then left for the developer to determine if they are still needed.
When I first came across these objects in my database, I was flummoxed. “Why would they leave such a mess?”, I asked myself. But they didn’t leave a mess. They were actually being considerate. Sometimes the SQL Objects have issues because of the changes being brought into the database. Since the original objects are kept as back up copies, you can go look at them before deleting them.
So how do you get rid of these objects once you have confirmed they are no longer needed?
Well, you create a script. This script is based on a version that my co-worker Chris Henry created. I’ve added to it and generalized it.
Let’s break it down
I found that ER/Studio Data Architect uses UTC as the time for the timestamp on the temporary objects. (That drove me nuts until I figured it out!). By using the GetUTCDate() function you’ll be able to programmatically create the date to filter the SQL objects that you want to get rid of.
DECLARE @Date AS varchar(10)
SELECT
@date = '%' + CONVERT(varchar(2), MONTH(GETUTCDATE()))
+ ( CASE
WHEN DAY(GETUTCDATE()) < 10 THEN '0'
ELSE ''
END )
+ CONVERT(varchar(2), DAY(GETUTCDATE()))
+ CONVERT(varchar(4), YEAR(GETUTCDATE())) + '%'
I then use UNION ALL to join the different types of objects with the proper DROP syntax.
SELECT
'ALTER TABLE [' + s.name + '].[' + p.name + '] DROP CONSTRAINT [' + o.name + ']' AS ItemToDrop
,o.type
FROM
sys.objects AS o
JOIN sys.objects AS p ON o.parent_object_id = p.object_id
JOIN sys.schemas AS s ON p.schema_id = s.schema_id
WHERE
o.name LIKE @date
AND o.type = 'F'
UNION ALL
SELECT
'Drop Trigger [' + s.name + '].[' + o.name + ']' AS ItemToDrop
,o.type
FROM
sys.objects AS o
JOIN sys.objects AS p ON o.parent_object_id = p.object_id
JOIN sys.schemas AS s ON p.schema_id = s.schema_id
WHERE
o.name LIKE @date
AND o.type = 'TR'
UNION ALL
SELECT
'drop table [' + s.name + '].[' + o.name + ']' AS ItemToDrop
,o.type
FROM
sys.objects AS o
JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE
o.name LIKE @date
AND o.type ='U'
UNION ALL
SELECT
'drop proc [' + s.name + '].[' + o.name + ']' AS ItemToDrop
,o.type
FROM
sys.objects AS o
JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE
o.name LIKE @date
AND o.type ='P'
ORDER BY
o.type
After it executes, I copy the statements out of the results pane and execute them.
Bonus Tip
Since this is a script you’ll use again and again…and again. You’ll want to turn it into a template. Two of the many ways to do that are as follows.
- SSMS comes with a Template Browser. You can save your script in the Template Browser for future use. Each you need it, simply double click on the script in the Template Browser and copy will be created for you. By adding the following code at the top of the script, you can use Ctrl+M to pick which database you want to use.
USE <DatabaseName, string,> GO
- If you are a SQL Prompt addict like me, then you can add your script as a snippet. Instead of adding the code I just showed you, add the following code and a default database name will appear as a default when you use Ctrl+M.
USE <DatabaseName, string,$DBNAME$> GO
You can download full script from here.
SQL Bacon Bits
SQL Tidbits: No.1– Outputting from ER/Studio Data Architect Directly to SQL Server Management Studio
