Archive for the ‘T-SQL’ Category

Getting started with Query Notifications with SQL Server 2008 R2

I’ve been experimenting with Query Notifications in SQL Server 2008 R2. This looks like a really cool way of implementing real-time features into your applications without constantly battering your database with select statements. Instead we can request a notification for when the data has changed for a query. Here’s a quick demo of the feature. […]

Inspect those indexes

Here’s a few queries I often use to review the indexes in our SQL Server systems. Tables should usually have a primary key. Are all of these intentional in your system? ?View Code TSQL– Tables with no primary key SELECT OBJECT_SCHEMA_NAME(t.[object_id]) AS [schema_name], OBJECT_NAME(t.[object_id]) AS [table_name] FROM sys.tables t LEFT JOIN sys.indexes i ON i.[object_id] […]

TSQL to help with mirroring failover

We ran a mock fail-over from our staging cluster to an offsite mirror server today. Here’s a few queries I used to help me get this done. I first took a backup of the SQL Agent jobs status on each server so I could easily return the jobs to their previous status on each server. […]

Use sys.sql_modules not INFORMATION_SCHEMA.ROUTINES

Have you ever tried using INFORMATION_SCHEMA.ROUTINES in SQL Server for searching for all procs referencing a specific object? Perhaps something like this; ?View Code TSQLSELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%table_name%’; Has this ever bitten you? If not then you’ve been lucky so far. The ROUTINE_DEFINITION column is limited to 4000 characters so you […]

Moving user databases the TSQL way

Here’s a few queries I built to construct the commands needed to move user database files in SQL Server 2208 R2. The queries are based on the procedure outlined here. As with all scripts on the Internet take care with this. It worked fine for my circumstances but may not in yours. Be careful and […]