Tagged: tsql

Case-sensitivity in a T-SQL WHERE clause

I was asked today if it was possible to use a case-sensitive LIKE statement in a WHERE clause. The default SQL Server collation is case-insensitive, but the collation used can be specified in a query. To return the collation of the current database:

SELECT DATABASEPROPERTYEX(DB_NAME(),'Collation');

In my case, this returned Latin1_General_CI_AS. The case-sensitive version of this is Latin1_General_CS_AS. To use this collation for a column, specify it after the column using the COLLATE statement. For example, I have a table called Groups, with 2 rows. The first row has a GroupName of “Admin”, and the value in the second row is “Standard”. The following query returns both rows:

SELECT GroupID ,
    GroupName
FROM dbo.Groups
WHERE GroupName LIKE '%a%'

Results:

GroupID	GroupName
1	Admin
2	Standard

However, when we use the COLLATE statement to use the case-sensitive collation, only the second row is returned:

SELECT GroupID ,
    GroupName
FROM dbo.Groups
WHERE GroupName COLLATE Latin1_General_CS_AS LIKE '%a%'

Results:

GroupID	GroupName
2	Standard

Practical uses for this could include exact string searches and password matching.

T-SQL to drop all Foreign Keys that reference a table

In a recent data migration project, I had a task to re-number some primary key columns for certain tables, so that there was no key overlap when a number of systems were combined. Part of this process involved identifying and dropping all the foreign keys that referenced the primary key on the tables that were being changed, then adding them again after the updates were completed. To save a bit of time, I knocked up this little script to generate the DROP and ALTER statements for the referencing foreign keys for a given table.

SELECT
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
		+ ' DROP CONSTRAINT ' + fk.NAME + ' ;' AS DropStatement,
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
	+ ' ADD CONSTRAINT ' + fk.NAME + ' FOREIGN KEY (' + COL_NAME(fk.parent_object_id, fkc.parent_column_id) 
		+ ') REFERENCES ' + ss.name + '.' + OBJECT_NAME(fk.referenced_object_id) 
		+ '(' + COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) + ');' AS CreateStatement
FROM
	sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.schemas s ON fk.schema_id = s.schema_id
INNER JOIN sys.tables t ON fkc.referenced_object_id = t.object_id
INNER JOIN sys.schemas ss ON t.schema_id = ss.schema_id
WHERE
	OBJECT_NAME(fk.referenced_object_id) = 'account'
	AND ss.name = 'dbo';

T-SQL Tuesday: Return a range of dates between 2 days

Just after Adam Machanic announced the first of the T-SQL Tuesday blog events, I was asked a question about how to return a list of all the dates between two given days.  I came across some good forum posts about how to do so, many of which utilised numbers tables.  I wanted to avoid that (nothing against them, just wanted something that was stand-alone), and figured that a CTE would be the best way to go; in fact someone had posted such a method, a little of which I have used here (if I can find the post again I’ll give them a mention in the comments!).

The following procedure takes 3 parameters: a StartDate, an EndDate and a bit flag to say if the range should be inclusive (that is, will it also return the StartDate and EndDate in the results), defaulted to 1.  A list of all the dates between the first two parameters will be returned.

The maxrecursion option has been set to 3660, giving a maximum of ten years dates to return.

create procedure dbo.GetDateRange
@StartDate datetime,
@EndDate datetime,
@Inclusive bit = 1
as

set nocount on;

declare @DayDiff smallint;
set @DayDiff = datediff(dd, @StartDate, @EndDate);
if @Inclusive = 0
set @DayDiff = @DayDiff – 1;

with cteRange (DateRange)
as (
select dateadd(dd, datediff(dd, 0, @EndDate) – @DayDiff, 0)
union all
select dateadd(dd, 1, DateRange)
from cteRange
where dateadd(dd, 1, DateRange) < (@EndDate + @Inclusive)
)
select DateRange
from cteRange
option (maxrecursion 3660);
go

Run the procedure using the following code:

exec dbo.GetDateRange '2009-12-07', '2009-12-12'

and the results are as shown in the image at the top of the post.

Hope some of you find this useful.  This post is part of Adam Machanic’s T-SQL Tuesday.

Identify the T-SQL being run by a SPID

This is one I used to use a lot, and had cause to use it this morning.  An ETL process to truncate a staging table was being blocked by another process on the server, and I needed to identify exactly what command the blocking process was attempting to execute.  I found the SPID by executing sp_who2, which showed me the SPID that was blocking the truncate command (in this case SPID 54).  I then executed the following command to find out exactly what command was causing the block to happen:

DBCC inputbuffer(54)

The result set returned shows the actual T-SQL that was last executed for that SPID, which was a very large insert from a remote PostgreSQL database. 

This is a quick and useful method for tracking down issues and investigating what is happening on your server.

Quick tip: Search for a term in a Stored Procedure

Need to find out if a term is referenced in any stored procedures in your database?  Use the following T-SQL statement…

select name, definition
from sys.procedures p
    inner join sys.sql_modules m
        on p.object_id = m.object_id
where
    m.definition like ‘%search term%';

This will work in SQL Server 2005 and 2008.