Tag Archive for Templates

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

 

Dev Connections – Demos and Slides Are Available

Thanks to all the Dev Connection attendees who came to my class. I have posted the slides, demos, and demo database on my Resources page.

%d bloggers like this: