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.
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.
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!
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).
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.