Archive for Script

SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition

First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Oops, I missed one.

I try to remember to add definitions as I create my tables so that I don’t forget later, but sometimes I miss one…especially definitions for attributes. Here is a great macro for ER/Studio Data Architect to find all the Entities and Attributes that are missing definitions. It even has a feature to colorize the tables that are missing definitions. I call it Verify Definitions For Selected Objects.

Note: Right now it only provides definitions for Entities and Attributes. I am happy to expand it if you need the other objects.
Option Explicit
'----------------------------------------------------------------------------
' PURPOSE:  	Creates a list of Entities and Attributes that are missing
'				definitions.
' Author:   	Mickey Stuewe
' Date:     	9/7/2013
'----------------------------------------------------------------------------
Sub Main
	'Defines the dialog box
	Begin Dialog UserDialog 680,322,"Verify Definitions for All Objects", .MyDialogAction
		Text 20,7,360,14,"Entities and Attributes that need Definitions",.Text1
		TextBox 20,28,500,287,.txtList,1
		PushButton 540,28,130,21,"Check",.CheckForDefinitions
		OKButton 540,49,130,21
		CheckBox 540,84,130,14,"Colorize Entities",.chkColor
	End Dialog

	Dim dlg As UserDialog

	'Calls the dialogbox
	Dialog dlg, -2
End Sub

Sub CheckForDefinitions()
    Dim objDiagram As Diagram
	Dim objModel As Model
	Dim objSubModel As SubModel
	Dim objSelObject As SelectedObject
	Dim objEntity As Entity
	Dim objAttr As AttributeObj

	Set objDiagram = DiagramManager.ActiveDiagram
	Set objModel = objDiagram.ActiveModel
	Set objSubModel = objModel.ActiveSubModel

	'Clears list
	DlgText("txtList","")

	' Iterate through all the selected objects in the current
	' model.
	For Each objSelObject In objSubModel.SelectedObjects
		Select Case objSelObject.Type
			Case 1 'Entity
				If objModel.Entities(objSelObject.ID).Definition = "" Then
					Call AddToList("Entity", objModel.Entities(objSelObject.ID).Owner & "." & objModel.Entities(objSelObject.ID).EntityName)
					If DlgValue("chkColor") = 1 Then
						objSubModel.EntityDisplays.Item(objModel.Entities(objSelObject.ID).TableName).BackgroundColor = RGB(243,222,248)
					End If

				End If
				For Each objAttr In objModel.Entities(objSelObject.ID).Attributes
					If objAttr.Definition = "" Then
						Call AddToList("Attrib", objModel.Entities(objSelObject.ID).Owner & "." & objModel.Entities(objSelObject.ID).EntityName & "." & objAttr.AttributeName)
						If DlgValue("chkColor") = 1 Then
						objSubModel.EntityDisplays.Item(objModel.Entities(objSelObject.ID).TableName).BackgroundColor = RGB(243,222,248)
						End If
					End If
				Next

			Case 16 'View
				MsgBox "It's time to write code for looking at Definitions for Views."
			Case 54 'Function
				MsgBox "It's time to write code for looking at Definitions for Functions."
		End Select
	Next objSelObject

End Sub

Sub AddToList(strObject As String, strObjectName As String)
	'Adds Entity or Attribute to list.
	DlgText("txtList",DlgText("txtList") & strObject & ": " & strObjectName & vbCrLf )

End Sub

'This is called for all actions in the dialog box. I will be traping for the button being clicked.
Private Function MyDialogAction(DlgItem As String, Action As Integer, SuppValue&) As Boolean

	If Action = 2 Then ' Value changing or button pressed
		Rem MyDialogFunc = True ' Prevent button press from closing the dialog box

		If DlgItem = "CheckForDefinitions" Then
			CheckForDefinitions
			MyDialogAction = True
		Else
			MyDialogAction = False
		End If
	End If
End Function

 

AdventCalendar2013Day7_Img1

 

Previous Post In Series: SQL Advent Calendar – Day 6 – What’s In All These Indexes?

Next Post In Series: SQL Advent Calendar 2013 – Day 8 – Creating Change Tracking Domains (1 of 3)

SQL Advent Calendar – Day 6 – What’s In All These Indexes?

AdventCalendar06First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Through the Looking Glass I see Many Indexes

Last year, I was doing quite a bit of query tuning. I was constantly looking at what indexes existed before I made new ones.  I created this snippet to Red Gates’ SQL Prompt to be able to quickly look at all the indexes for a given table. Sometimes I even found duplicate indexes when uses this snippet. It’s one of my favorites. I call it Indexes4Table.

--Use Shift + Ctrl + M to set Template Placeholders
USE <database,sysname,$dbname$>
GO
/* ***************************************************
Purpose:    Lists all indexes for a table
Author:     mickey stuewe www.mickeystuewe.com
Date:       12/6/2013
*************************************************** */
SELECT
	i.name AS IndexName
	,i.type_desc
	,STUFF(Indexed.ColumnList, 1, 1, '') AS IndexColumnList
	,STUFF(Included.ColumnList, 1, 1, '') AS IncludeColumnList
	,i.filter_definition
	,i.fill_factor
FROM
	sys.indexes AS i
	OUTER APPLY
			(
				SELECT
					',' +  CASE 
							WHEN ic.is_descending_key = 1 THEN col.name + ' DESC'
							ELSE col.name  
							END 
				FROM
					sys.index_columns AS ic 
					INNER JOIN sys.columns col ON ic.object_id = col.object_id
													AND ic.column_id = col.column_id
				WHERE
					ic.index_id = i.index_id
					AND ic.object_id =  i.object_id
					AND ic.is_included_column = 0
				ORDER BY
					ic.index_column_id
				FOR XML	PATH('')
			) AS Indexed(ColumnList)
	OUTER APPLY
			(
				SELECT
					',' +  CASE 
							WHEN ic.is_descending_key = 1 THEN col.name + ' DESC'
							ELSE col.name  
							END 
				FROM
					sys.index_columns AS ic 
					INNER JOIN sys.columns col ON ic.object_id = col.object_id
													AND ic.column_id = col.column_id
				WHERE
					ic.index_id = i.index_id
					AND ic.object_id =  i.object_id
					AND ic.is_included_column = 1
				ORDER BY
					ic.index_column_id
				FOR XML	PATH('')
			) AS Included(ColumnList)
WHERE
	i.object_id =  OBJECT_ID(N'<schema_name,sysname,dbo>.<table_name,sysname,>')
ORDER BY
	i.type_desc
	,Indexed.ColumnList

There are a couple of things I want you to notice. I’m using a string concatenation pattern based on Peter Larson’s (b) SQL, which is written about on Adam Machanic’s (b|t) blog here. I also wanted you to notice that you can use placeholders within a string. See line 57.

I ran my script against the Persons.Person table in AdventureWorks2008R2 (I also added an index so that you can see all the columns filled out. Here is the outcome.

AdventCalendar2013Day6_Img1

Now I can quickly look at all the values that I find important for my indexes on a particular table.

Previous Post In Series: SQL Advent Calendar – Day 5 – Snippet For New Indexes

Next Post In Series: SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition

 

SQL Advent Calendar – Day 5 – Snippet For New Indexes

AdventCalendar05First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

New Index

I have four snippets that are templates for new SQL Objects. The one I’m sharing today is for new indexes. The great thing about  snippets like this, is I add comments to help me or my teammates make decisions on how the SQL object is created.

Notice that I included the hint of “Use Shift + Ctrl + M to set Template Placeholders”. I wrote about them here. I start all of my “new” templates with the word new, but you can really call them whatever you want. This one is NewIndex. Notice that some of the Template Placeholders are repeated. That keeps me from having to do repetitive work. (WIN!)

USE <db,string,$dbname$>
GO

--Use Shift + Ctrl + M to set Template Placeholder
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<tablename,string,>') AND name = N'IX_<tablename,string,>_<fields,string,>')
DROP INDEX IX_<tablename,string,>_<fields,string,> ON <schemaowner,string,dbo>.<tablename,string,> WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX IX_<tablename,string,>_<fields,string,> ON <schemaowner,string,dbo>.<tablename,string,>
(
	$CURSOR$<fields,string,> 
)
<optional_include,string,include()>
<optional_where,string,where()>
WITH 
	(FILLFACTOR = <fillfactor,string,95>)ON [PRIMARY] --GUID Datatype use FillFactor = 80, otherwise use FillFactor = 95 
GO

Here is what the dialog box will look like as I fill it out.

AdventCalendar2013Day5_Img1

Here is what the code will look like after I click OK. Notice that the INCLUDE clause is filled out and the WHERE clause has been omitted. One “gotcha” for this, is the list of fields. You have to either replace the underscores with commas in the list or replace the commas with underscores in the name. It still makes my life easy.

AdventCalendar2013Day5_Img2

 

Previous Post In Series: SQL Advent Calendar 2013 – Day 4–Comments Are Like Love Notes to Your Future Self

Next Post In Series: SQL Advent Calendar – Day 6 – What’s In All These Indexes?

SQL Advent Calendar 2013 – Day 4 – Comments Are Like Love Notes to Your Future Self

AdventCalendar04First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

It’s a Dark and Snowy Day

It’s a dark and snowy day and you get an urgent email. There’s a stored procedure that has been running for 90 minutes. It needs to be fixed STAT. You open up the monstrosity (which hasn’t been cracked open in four years) and it looks like Data’s cat threw up in it. The first thing you want to know, is who wrote the thing? The second thing is how can you possibly make the 400 lines legible quickly? It takes 3 emails and 4 phone calls to track down the authors (plural) of the stored procedure and you are finally able to understand the purpose of the code.

Wouldn’t it have been nice if there had been comments? The author’s (plural) names would have helped as well. Below are three SQL Prompt snippets to help with minimal effort, just a couple of keystrokes and the aid of SQL Prompt.

As for making the code legible, that can be done with 3 little keys. CTRL+K+Y. This short cut will format your code based on the settings you dictate. Everything from indentation of certain parts of the code to the placement of commas.
Before

ALTER PROCEDURE [dbo].[SalesTeamAssignment_rptUGLY](@Group AS varchar(1000), @StartDate AS date, @EndDate AS date)AS
SET NOCOUNT ON;
DECLARE @UselessVar AS varchar(max)
SELECT
p.BusinessEntityID ,FirstName,LastName,s.Name AS Store,[Group] AS TerritoryGroup,cr.Name AS CountryRegion,st.Name AS Territory,CustomerCount,s.*
FROM
AdventureWorks2008R2.sales.salesperson AS sp JOIN AdventureWorks2008R2.Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID
JOIN AdventureWorks2008R2.sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID JOIN [AdventureWorks2008R2].[Person].[CountryRegion] AS cr ON st.CountryRegionCode = cr.CountryRegionCode
JOIN AdventureWorks2008R2.Sales.Store AS s ON s.SalesPersonID = sp.BusinessEntityID JOIN dbo.fn_Split(',', @Group) AS grps ON st.[Group] = CONVERT(nvarchar(50), grps.SeparatedValue)
OUTER APPLY (SELECT COUNT(*) AS CustomerCount FROM AdventureWorks2008R2.sales.Customer AS c WHERE s.BusinessEntityID = c.StoreID ) AS cc;
But wait, there’s more…

If you also use another 3 key shortcut, CTRL+B+Q, you can alias all the fields, which will also make it easier to read.

Note: If you don’t have the table names aliased, then the entire table name and schema name will be used as the alias.
After
ALTER PROCEDURE [dbo].[SalesTeamAssignment_rptUGLY]
	(
	 @Group AS varchar(1000)
	,@StartDate AS date
	,@EndDate AS date
	)
AS
	SET NOCOUNT ON;
	DECLARE	@UselessVar AS varchar(max)
	SELECT
		p.BusinessEntityID
	   ,p.FirstName
	   ,p.LastName
	   ,s.Name AS Store
	   ,st.[Group] AS TerritoryGroup
	   ,cr.Name AS CountryRegion
	   ,st.Name AS Territory
	   ,cc.CustomerCount
	FROM
		AdventureWorks2008R2.sales.salesperson AS sp
		JOIN AdventureWorks2008R2.Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID
		JOIN AdventureWorks2008R2.sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
		JOIN AdventureWorks2008R2.Person.CountryRegion AS cr ON st.CountryRegionCode = cr.CountryRegionCode
		JOIN AdventureWorks2008R2.Sales.Store AS s ON s.SalesPersonID = sp.BusinessEntityID
		JOIN dbo.fn_Split(',', @Group) AS grps ON st.[Group] = CONVERT(nvarchar(50), grps.SeparatedValue)
		OUTER APPLY (
					 SELECT
						COUNT(*) AS CustomerCount
					 FROM
						AdventureWorks2008R2.sales.Customer AS c
					 WHERE
						s.BusinessEntityID = c.StoreID
					) AS cc;
Future Love Notes

The first snippet I like to use, I call com. It adds a comment block to the query. I even have this added to my templates for new stored procedures, but I’ll share that one later in the month. I love this header because it utilizing the relatively new placeholders, $NAME$ and $DATE$, which I wrote about on Day 1.

Note: $DATE$ also has the ability to have a custom format. An example would be $DATE(MM/dd/yyyy)$. This would create the date 12/04/2013 for today.
/* ***********************************************************************************
Purpose:    $CURSOR$[Brief Description]
Notes:      
Sample:    
            [Sample Call of procedure with sample parameter values]
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */

The second snippet is used when you need to modify an existing script. I call it comhist. I personally don’t mind long names, because the intellisence will show me my available options as I type. I usually only need com+<down arrow>+<down arrow>+ tab. This snippet uses  $CURSOR$, $NAME$ and $DATE$. The one downside, is the snippets won’t work inside a comment. (I think I may have to mention that to Red Gate.)

--$DATE$		$USER$			$CURSOR$

The third snippet is used to add a TODO to your code. I use it when I can’t focus on a particular part of the code, but I need a reminder for later. You can then use SQLSearch ( a free tool that can be downloaded from Red Gate) to find all the locations of TODO.

/*TODO: $CURSOR$ - $USER$ - $DATE$ */

Here is what the look like after using them.

/* ***********************************************************************************
Purpose:    [Brief Description]
Notes:      
Sample:    
            [Sample Call of procedure with sample parameter values]
Author:     mstuewe
Date:       12/4/2013

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
12/4/2013		mstuewe			This is a change in the code
************************************************************************************ */

/*TODO: Buy Christmas Presents. - mstuewe - 12/4/2013 */
Note: You can download these snippets from my Script Library under Resources.

Previous Post In Series: SQL Advent Calendar 2013 – Day 3 – Organize Before All The New Toys Come In

Next Post In Series: SQL Advent Calendar – Day 5 – Snippet For New Indexes

SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect

First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

AdventCalendar02As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Behind Door Number Two

ER Studio Data Architect by Embarcadero (ER Studio DA) has the ability to create macros, which makes me absolutely happy. Why? Because I don’t like repetitive work. If I can create a macro, then I can have the computer do the repetitive work while I go get a peppermint mocha.

One day I was working on a macro that created fields for multiple tables. I realized during the development process that I also needed a macro to delete fields for multiple tables. So I created the macro below. It has come in handy many times.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro adds base attributes to entities that are selected.
' Author:   	Mickey Stuewe
' Date:     	9/3/2013
' version:      1.0
'----------------------------------------------------------------------------

Sub Main

Dim objModel As Model
Dim objSelObject As SelectedObject
Dim objEntity As Entity
Dim iCounter As Integer

Begin Dialog UserDialog 440,98,"Continue?" ' %GRID:10,7,1,1
	Text 20,21,250,28,"Are you sure you want to remove a field from the selected tables?",.lblOwner
	OKButton 300,21,120,28
	CancelButton 300,56,120,28
	Text 20,56,80,14,"Field Name:",.Text1
	TextBox 100,56,170,14,.txtFieldName
End Dialog

Dim dlg As UserDialog

If Dialog(dlg) = -1 Then

	Set objModel = DiagramManager.ActiveDiagram.ActiveModel

	' Iterate through all the selected objects in the current
	' model.
	For Each objSelObject In objModel.ActiveSubModel.SelectedObjects

		'Get the object type - we are only concerned
		'with entities.
		If objSelObject.Type = 1 Then

			Set objEntity =  objModel.Entities.Item(objSelObject.ID)

			objEntity.Attributes.Remove(dlg.txtFieldName)

			iCounter = iCounter + 1
		End If
	Next objSelObject

	MsgBox dlg.txtFieldName & " was removed from " & iCounter & " Table(s).", vbOkOnly + vbInformation, "Attributes Removed From Tables"
End If

End Sub

To use this macro, follow the steps below.

  1. Add the macro to the library. (Tomorrow I’ll be expanding on this step.)
  2. Add the macro to the shortcut menu for Tables.
  3. Select several tables that have a common field you would like to remove.
  4. Implement your new macro
  5. Watch the unwanted fields go away. (When I do this to 30 tables at one time, I actually feel giddy. True story.)
Note: You can download this macro from my Script Library under Resources. It’s called Remove Attribute From Selected Tables.

Next Post In Series: SQL Advent Calendar 2013 – Day 3 – Organize Before All The New Toys Come In

SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

AdventCalendar01Today my daughter Natalie and I started our first LEGO Advent Calendar – Star Wars edition. While this particular calendar is not Christian centric, it’s a lot of fun for us. For those that aren’t familiar with an Advent Calendar, it’s a calendar that counts down the days until Christmas. Each day a small gift or message is given.

My husband grew up with a handmade Advent Calendar. He told me when we were engaged that he wanted one for his family to use. Since we got married 6 weeks after I graduated from college and I had no money, I made one for my wedding present to him. As I was hanging it up today, I thought I would make one for you, my readers. Over the next 25 days, I’ll share my scripts with you from two of my favorite products as well as tips on how to create your own.

SQL Prompt By Red Gate

One of my favorite tool companies is Red Gate out of the UK. I not only love their tools, but I love their service. They care not only about their products, but what we think of their products and how we use their products. Over the last year I have had the honor to be part of their FoRG group (Friends of Red Gate). As a FoRG member, I get to give my suggestions directly to the project managers and others seeking insight from the FoRG group. Don’t think you can’t give your two cents too. Red Gate loves to hear from everyone. In fact, at PASS Summit this year they dedicated their whole back wall to receiving sticky notes for ideas on how to improve all their products. By the end of the conference, it was quite full.

SQL Prompt was the first of their products that I used. I use it just about every time I’m in SSMS, and have been very fortunate to be able to use it at my new job. (It’s very frustrating when I can’t use it.)

SQL Prompt does a couple of things for you. The most obvious thing that it does, is help you write SQL faster. Yes, SQL 2012 has intellisence, but SQL Prompt has a better version. The second feature that I love is the code standardization options. There are several settings and shortcut keys that you can use to standardize your code. The third feature that I love and will be featuring in my SQL Advent Calendar, are templatized scripts (called snippets). The snippets are accessed by keystrokes that you specify. A library of snippets comes with SQL Prompt, but you can also add your own.

ER/Studio Data Architect by Embarcadero

Embarcadero has a very wide range of products, and one of those is on my favorite tool list. I’m sure you can guess what it is based on my last couple of posts here and here. It’s Embarcadero’s ER/Studio Data Architect.

ER/Studio Data Architect is a database modeling tool. It’s a great product to use as the master copy of a database schema. It also has the ability to create scripts for different versions of databases. This came in real handy when I needed to script a database model for both SQL 2000 and SQL 2012.

One of the features that I really like ER/Studio Data Architect, is the ability to create and use macros inside the product to update the database model. All you need to know is VB script and the Object Model.

So, without further ado…..

Day 1 – Placeholders in SQL Prompt snippets

You may ask why I like the SQL Prompt snippets since SQL 2012 has a template library that is very similar. There are currently 8 reasons why I like them. ($DATE$, $TIME$, $USER$, $PASTE$, $MACHINE$, $CURSOR$, $SERVER$, and $DBNAME$) These are the 8 placeholders that can be used in conjunction with the snippets. As each name suggests, they represent a piece of information that is unique. $USER$ will be replaced by the name of the User logged into SSMS and $DBNAME$ will be replaced by the name of the currently active database. $PASTE$ will grab what is in your copy buffer and $CURSOR$ will move the cursor to the that location in your snippet. (An important thing to remember is that the placeholders are case sensitive.)

Note: There are 8 placeholders with version 6.1, which is the current version. Other versions may have a different set with different functionality. Visit the documentation for the version you have to see the current list.

To create an SSMS placeholder you use the syntax below.

<X,Y,Z>
  • Where X is the name of the placeholder and should be very descriptive. It will be viewed in a dialog box with all the other placeholders
  • Y is the data type of the placeholder. Most of the time I use “string”
  • Z is the default value for the placeholder and is optional, but you still need to have the third parameter position available. This is where you can really leverage the SQL Prompt Placeholders.

In the example below I created three SSMS placeholders and I use three different SQL Prompt placeholders. Two of the SSMS placeholders are identical, so they will appear as one placeholder to the user. Notice that $DBNAME$ is used inside the SSMS placeholder. This will allow the current database name to be the default for that placeholder.

USE <Database Name,string,$DBNAME$>
GO

--Created by: $USER$ on $DATE$

SELECT
     name
FROM
     <Database Name,string,$DBNAME$>.sys.objects AS o
WHERE
     name LIKE '<Search Criteria,string,>%'

To use the code above, copy it into a new snippet in SQL Prompt.

  1. Open up the Snippet Manager.
  2. Click the New button.
  3. Name the snippet TNS.
  4. Set the description Table Name Search.
  5. Save the script.

In a new query window type TNS+. Notice that your SQL credentials were put in place of $USER$. Today’s date was put in place of $DATE$ and the current database was put in place of $DBNAME$.

AdventCalendar2013Day1_Img1

Now let’s leverage the SSMS placeholders by typing ++M. You can now accept the default values, or you can change them. After you click OK, you can run the script.

That’s a wrap

I hope you enjoyed the first day of 2013 SQL Advent Calendar.

Note: Since I live near the end of the GMT day, the calendar will be done based on the PST zone.

Next Post In Series:

SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect

 

SQL Bacon Bits No. 2 – Dropping Temporary SQL Objects

Bacon file8121243652304This 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

 

%d bloggers like this: