Today I’m sharing a couple useful SQL Queries for SQL Server that I have found useful.
Sometimes you need to disconnect all users from a specific database in order to make some changes.
SELECT ‘KILL ‘ + CONVERT (VARCHAR(10), SPID)
FROM Master..SYSDATABASES
WHERE DBID = DB_ID(‘MyDatabase’)
This query returns an SQL Server query to kill every process currently connected to the data base named MyDatabase. Take the results of the query, paste it into a new query window, and execute the commands.
When you have a big table and you want to get the number of records you could execute
SELECT COUNT(1)
FROM MyTable
This is an accurate query. However, on a large table it will take a while to complete. Luckily, there is a much faster technique to get the number of records in a table, because SQL Server keeps the count in the metadata of the indexes. Here is a much faster query.
SELECT MAX(ROWS)
FROM MyDatabase..SYSINDEXES
WHERE ID = OBJECT_ID(‘MyTable’)
Since the OBJECT_ID function references the currently attached database when the query is executed, in order for this query to work you must currently be pointing to the database MyDatabase. This query returns the number of rows from the index having the most rows. If you need to execute the query with a connection pointing to a different database you run the following query instead
SELECT MAX(SI.ROWS)
FROM MyDatabase..SYSINDEXES SI
JOIN MyDatabase..SYSOBJECTS SO ON SI.ID = SO.ID
WHERE SO.NAME = ‘MyTable’
This query can be modified to use the newer replacements for SYSINDEXES and SYSOBJECTS.
Cheers,
Ben