The following snippet allows you to find all stored procedures containing a specific word or words. I found it to be very helpful to find all references to a table I needed to remove from my database. I order not to break functionality I scanned all procs for references and changed or removed them. Here [...]

A colleague of mine found this little gem of a query (sorry, don’t know the original author). It finds the 20 most expensive queries executed on a SQL server instance, sorted by execution time. CPU load is also returned by the query. Here it is: SELECT TOP 20 qs.sql_handle, qs.execution_count, qs.total_worker_time AS Total_CPU, total_CPU_inSeconds = –Converted from microseconds qs.total_worker_time/1000000, average_CPU_inSeconds = –Converted from microseconds (qs.total_worker_time/1000000)/ qs.execution_count, [...]

It’s a documented bug in SQL server that causes above error message. The error is triggered by nested comment blocks, like this example: /* some comments /* nested comments*/ some more comments */ All very well, it’s a documented bug so it shouldn’t be a problem. Microsoft’s solution is plain and effective: don’t use nested comment [...]

There are a few ways to retrieve the table sizes (both row count and size in bytes) in SQL server. The simplest is the sp_spaceused stored procedure which takes a table name as argument: EXEC sp_spaceused ‘table_name’ This is all fine, but what if you want to display info about a dozen or more tables? [...]

Easy peasy, use the sp_rename stored procedure: EXEC sp_rename ‘TABLENAME.OLD_COLUMNNAME’, ‘NEW_COLUMNAME’, ‘COLUMN’;

Here’s an easy, single line statement to check if a database exists in the current SQL-server instance: IF db_id(’db_name’) IS NOT NULL

Here’s a script to retrieve the name of the currently running SQL Server instance: SELECT @@SERVERNAME AS ‘ServerName’ Resultset: ServerName ————————— DEV-SQL (1 row(s) affected)

It can sometimes be handy to know in which database you are working. To get the name of the current database you can use a simple script: SELECT DB_NAME() AS DataBaseName Resultset: DatabaseName ———————— NorthWind (1 row(s) affected)

OpenX is a great tool for displaying ads on your website. But there is a small issue I’ve been wanting to address: statistics data. OpenX gathers a lot of data about clicks and impressions of your banners. While this can be very helpful, it also puts a strain on the database. And as I use [...]

I’ve posted a snippet before that allows you to select a list of tables (or views, stored procedures, etc) from a SQL-Server database. It would be only logical to also have a snippet that allows you to select the columns in the table you’re interested in. The following piece of SQL code does just that. [...]

Using the MultipleActiveResultSets setting in a connection string allows one to have multiple datareaders open on the same connection, at the same time. This setting was introduced with SQL server 2005. Setting it with SQL server 2000 does not have any effect, or so I thought.

If you happen to forget your SQL Server password for ‘sa’ account, then here’s a simple query to help you reset it: ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master] GO USE [master] GO ALTER LOGIN [sa] WITH PASSWORD=N’MyNewPassword’ MUST_CHANGE GO In Case you remember your Old Password and want to change the ‘sa’ password, use this query: [...]

The following SQL snippet extracts the text between <H3> and </H3> in the raw column: SELECT mid(LEFT(raw, locate(”, raw)-1), locate(’<h3>’, raw)+4) FROM table</h3>

In a production environment we have a 64-bit SQL Server Std edition running on Windows Server 2003 x64 Enterprise. The box used to contain 16GB of RAM, but to improve performance we upgraded to 64GB. As suggested by Books Online, SQL Server needs permission to ‘lock pages in memory’ to take advantage of the available [...]

Microsoft products such as Windows Server Update Services (WSUS) 3.0 and Windows Sharepoint Services (WSS) 3.0 ship with SQL Server 2005 Embedded Edition, also called Windows Internal Database.The Windows Internal Database is an embedded data service that can only be used by a handful of Windows Services. It is designed in such a way that [...]