Scott On Writing.NET : SQL

Searching SQL Server Stored Procedure and Trigger Text

Scott Mitchell — Fri, 30 Sep 2011 16:16:55 GMT

While I like to consider myself a web developer, every now and then I have to put on my DBA hat to address some SQL related issue. This little script has saved my butt more than once. It searches the text of triggers, UDFs, stored procedures and views for a particular substring, returning the name and type of those database objects that match.

DECLARE @Search varchar(255)
SET @Search='text_to_search'

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

The above script is one of many in my “bag of scripts” I’ve collected over the years. This particular gem was snagged from Stackoverflow: How to find a text inside SQL Server procedures / triggers?

Removing Gaps and Duplicates from a Numeric Column in Microsoft SQL Server

Scott Mitchell — Tue, 18 Jan 2011 17:35:28 GMT

Here’s the scenario: you have a database table with an integral numeric column used for sort order of some other non-identifying purpose. Let’s call this column SortOrder. There are a many rows in this table. Every row should have a unique, sequentially increasing value in its SortOrder column, but this may not be the case – there may be gaps and/or duplicate values in this column.

For example, consider a table with the following schema and data:

EmployeeID	Name	SortOrder
1	Scott	1
2	Jisun	8
3	Alice	7
4	Sam	7
5	Benjamin	3
6	Aaron	9
7	Alexis	4
8	Barney	5
9	Jim	5

Note how the SortOrder column has some gaps and duplicates. Ideally, the SortOrder column values for these nine rows would be 1, 2, 3, …, 9, but this isn’t the case. Instead, the current values (in ascending order) are: 1, 3, 4, 5, 5, 7, 7, 8, 9.

Our task is to take the existing SortColumn values and get them into the ideal format. That is, after our modifications, the table’s data should look like so:

EmployeeID	Name	SortOrder
1	Scott	1
2	Jisun	8
3	Alice	6
4	Sam	7
5	Benjamin	2
6	Aaron	9
7	Alexis	3
8	Barney	4
9	Jim	5

Note how now there are now no gaps or duplicates in SortOrder.

The Solution: Ranking Functions, Multi-Table UPDATE Statements and Common Table Expressions (CTEs)

Microsoft SQL Server 2005 added a number of ranking functions that simplify assigning ranks to query results, such as associating a sequentially increasing row number with each record returned from a query or assigning a rank to each result. For example, the following query – which uses SQL Server’s ROW_NUMBER() function – returns the records from the Employees table with a sequentially increasing number associated with each record:

SELECT Name, 
       SortOrder, 
       ROW_NUMBER() OVER (ORDER BY SortOrder) AS NoGapsNoDupsSortOrder
FROM Employees

The above query would return the following results. Note how the data is sorted by SortOrder. There’s also a new, materialized column (NoGapsNoDupsSortOrder) that returns sequentially increasing values.

Name	SortOrder	NoGapsNoDupsSortOrder
Scott	1	1
Benjamin	3	2
Alexis	4	3
Barney	5	4
Jim	5	5
Alice	7	6
Sam	7	7
Jisun	8	8
Aaron	9	9

What we need to do now is take the value in NoGapsNoDupsSortOrder and assign it to the SortOrder column. If we had the above results in a separate table we could perform such an UPDATE, as SQL Server makes it possible to update records in one database table with data from another table. (See HOWTO: Update Records in a Database Table With Data From Another Table.)

While the results in the above grid are not in a table (but are rather the results from a query), the good news is that we can treat those results as if they were results in another table using a Common Table Expression (CTE). CTEs, which were introduced in SQL Server 2005, can be thought of as a one-off view; that is, a view that is created, defined, and used in a single SQL statement.

Putting it all together, we end up with the following UPDATE statement:

WITH OrderedResults(EmployeeId, NoGapNoDupSortOrder) AS 
(
    SELECT EmployeeId, 
              ROW_NUMBER() OVER (ORDER BY SortOrder) AS NoGapNoDupSortOrder
    FROM Employees
)
UPDATE Employees
    SET SortOrder = OrderedResults.NoGapNoDupSortOrder
FROM OrderedResults
WHERE Employees.EmployeeId = OrderedResults.EmployeeId AND 
       Employees.SortOrder <> OrderedResults.NoGapNoDupSortOrder

The above query starts by defining a CTE named OrderedResults that returns two column values: EmployeeId and NoGapNoDupSortOrder. It then updates the Employees table, setting its SortOrder column value to the NoGapNoDupSortOrder value where the Employees table’s EmployeeId value matches the OrderedResults CTEs EmployeeId value (and where the SortOrder does not equal the NoGapNoDupSortOrder).

For more information on CTEs, ranked results, and updating one table (Employees) with data from another table or CTE (OrderedResults), check out the following resources:

Happy Programming!

HOWTO: Update Records in a Database Table With Data From Another Table (MS SQL Server)

Scott Mitchell — Tue, 13 Jul 2010 04:01:00 GMT

SQL's UPDATE statement makes it easy to update one or more records in a database table. The most common UPDATE statement pattern assigns static or parameterized values to one or more columns:

UPDATE MyTable SET
    Column1 = Value1,
    Column2 = Value2,
    ...
WHERE PrimaryKeyColumn = SomeValue

But what if you need to update values in one database table from values in another database table? There are a couple ways this can be done. One way is to use a subquery:

UPDATE MyTable SET
    Column1 = (SELECT SomeColumn FROM SomeOtherTable WHERE SuchAndSuch = ThisAndThat),
    Column2 = (SELECT SomeOtherColumn FROM SomeOtherTable WHERE SuchAndSuch = ThisAndThat),
    ...
WHERE PrimaryKeyColumn = SomeValue

Alternatively, you can use a JOIN statement in your UPDATE clause. The precise syntax seems to vary a bit based on the database system. For Microsoft SQL Server, use the following syntax:

UPDATE MyTable SET
    Column1 = SomeOtherTable.SomeColumn,   
    Column2 = SomeOtherTable.SomeOtherColumn,
    ...
FROM MyTable
    INNER JOIN SomeOtherTable ON
        MyTable.SuchAndSuch = SomeOtherTable.ThisAndThat
WHERE PrimaryKeyColumn = SomeValue

Bear in mind that updating data in one table with data from another table may be a sign of bad database design. I've seen developers use these approaches to duplicate data across multiple tables so that it's "easier" or "quicker" to write a query to read the data, but such actions are (usually) folly. Data duplication may make your SELECT queries easier to write, but you can very easily end up with data integrity issues.

So when might you need to copy data from one table to another? Consider a help desk application that allows support staff to create support tickets, where each ticket in the system exists as a record in the Tickets table. A ticket may have dozens of data points. To ease data entry, let's say that administrative users can create "ticket templates," which pre-define the values for a number of ticket data points that are stored in a table named TicketTemplates. Applying a template to an existing ticket would require that the existing record in the Tickets table have its non-set data points assigned to the corresponding default values of the selected template. Presume that the Tickets table has a TicketTemplateId field that indicates what template is to be applied to the ticket. Once this value has been assigned, the following UPDATE statement could be executed to copy the template's pre-defined values to the non-assigned data points in the Tickets table:

UPDATE Tickets SET
    PriorityId = ISNULL(Tickets.PriorityId, TicketTemplates.PriorityId),
    CategoryId = ISNULL(Tickets.CategoryId, TicketTemplates.CategoryId),
    IsPublic = ISNULL(Tickets.IsPublic, TicketTemplates.IsPublic),
    ...
FROM Tickets
    INNER JOIN TicketTemplates ON
        Tickets.TicketTemplateId = TicketTemplates.TicketTemplateId
WHERE Tickets.TicketId = @TicketId

Happy Programming!