List the connections to a database (sp_who saved into a table)

Cool little stored procedure that you can install anywhere but I have installed on the Temp database and it tells you who is connected to a database.

It uses sp_who but puts the results into a database that you can query but really all we ever want to know is who is accessing a particular database and sp_who has always been restrictive in that sense.

Easy to call:

EXEC Temp.dbo.ListConnections @sDatabaseName = 'MyDatabaseName'
 
Hope that helps, Jon Bosker, DB Gurus, 15 Dec 2015



CREATE PROCEDURE dbo.ListConnections
(
    @sDatabaseName varchar(MAX)
)
AS

-- Create a table to store the results:
DECLARE @Who TABLE (
          [spid]        int
        , [ecid]        int
        , [status]    varchar(50)
        , [loginame]    varchar(255)
        , [hostname]    varchar(255)
        , [blk]        varchar(50)
        , [dbname]    varchar(255)
        , [cmd]        varchar(255)
        , [request_id] int
        )

-- Put the results of sp_who in there:
INSERT INTO @Who EXEC sp_who 



-- Query that table to get the connections to the specified database
SELECT * FROM @Who WHERE dbname = @sDatabaseName
GO

Script to index all foreign key columns for

Depending on your database design and number of rows in the table a possibly good performance improvement is to index all columns that refer to a primary key so that both ends of the relationship are indexed.  This script takes the grunt work out of it by using the database schema to find those columns and then index them.

I have tried to comment it to explain how it works. If anyone know where the monster SQL statement comes from please let me know and I will credit them accordingly.

Hope that helps, Jon Bosker, DB Gurus, 26 Nov 2015



--------------------------------------------------------------
-- Script to create indexes on all columns that have a foreign key constraint
-- note that this only works for single column foreign keys (all we use)
--------------------------------------------------------------
-- create a table variable to put the SQL commands for subsequent running:
DECLARE @t TABLE
(
SQLCommand varchar(8000),
id int identity(1,1)
)

-- This monster select gets a list of all columns that have a foreign key
-- (sorry I cannot remember where I got it from originally) 
INSERT INTO @t (SQLCommand)
SELECT DISTINCT
'CREATE NONCLUSTERED INDEX [IX_' + CU.COLUMN_NAME + '] ON [' + FK.TABLE_NAME + '] (['+CU.COLUMN_NAME+'] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]' AS [IndexCommand]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU  ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
  ) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
LEFT JOIN (
SELECT I.*
FROM sys.indexes I
JOIN sys.objects O ON I.object_id = O.object_id 
) INX 
ON INX.name LIKE '%' + PK.TABLE_NAME + '%' and INX.type_desc = 'NONCLUSTERED' AND INX.name LIKE 'IX%'
WHERE INX.name IS NULL     


-- Now we just need to run it line by line
DECLARE @sSQL varchar(8000)
DECLARE @counter int
SET @counter = 1


WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL = SQLCommand  FROM @t WHERE id = @counter
PRINT @sSQL
EXEC (@sSQL)  -- comment out this line to test it
SET @counter = @counter + 1
END 

Script to delete all stored procedures or procedures with a certain name pattern

Useful script to delete all stored procedures or all procedures with a certain name pattern e.g. all that start with sp etc.  Developed for SQL Server 2008 R2  

2 parts: the first creates a table variable and puts in there the names of the SPs to delete from the information schema. Part 2 cycles through that table variable executing the DROP PROCEDURE command.

Hope that helps, Jon Bosker, DB Gurus, 25 Nov 2015



--------------------------------------------------------------
-- You can set a name pattern of stored procedures to delete:
-- e.g SET @sPattern = 'sp%' to just delete procedures that start with sp
DECLARE @sPattern varchar(MAX)
SET @sPattern = '%'  --<<-- all SPs

DECLARE @t TABLE
(
SPName varchar(8000),
id int identity(1,1)
)

INSERT INTO @t
SELECT SPECIFIC_SCHEMA  + '.[' + SPECIFIC_NAME + ']' FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME LIKE @sPattern


-- SELECT * FROM @t

DECLARE @sSQL varchar(8000)
DECLARE @counter int
SET @counter = 1


WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL = 'DROP PROCEDURE ' + SPName  FROM @t WHERE id = @counter
PRINT @sSQL
EXEC (@sSQL)  -- comment out this line to test it first
SET @counter = @counter + 1
END 

Script to find out Disk Space Per Table

Useful script to find out how much disk space each table takes up by querying the system tables.

Hope that helps, Jon Bosker, DB Gurus, 23 Nov 2015


SELECT 
T.name AS [TableName],
SUM(p.rows) AS [RowCount],
((SUM(a.data_pages) * 8) / 1024) AS SpaceUsedMB
FROM sys.tables T
JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
JOIN sys.partitions P ON I.object_id = P.OBJECT_ID AND I.index_id = P.index_id
JOIN sys.allocation_units A ON p.partition_id = A.container_id
WHERE T.name NOT LIKE 'dt%' 
AND I.OBJECT_ID > 255 
AND I.index_id <= 1
GROUP BY T.NAME, I.object_id, I.index_id, I.name 
ORDER BY ((SUM(a.data_pages) * 8) / 1024) DESC

Count Database Connections, List Database Connections

Useful script to count the number of connections by user. Also lists the connections to the database. We used this to find a problem where the database performance was very slow (frozen you could say). It turned out to be caused by the application not releasing the connection once it had finished.

Hope that helps, Jon Bosker, DB Gurus, 18 Nov 2015


DECLARE @DatabaseName varchar(MAX)
SET @DatabaseName = 'MyDatabase' --<< put your database name in here

-- Count the number of connection by login
SELECT loginame, COUNT(*)
FROM master..sysprocesses
WHERE dbid=db_id(@DatabaseName)
GROUP BY loginame

-- List the connections
SELECT *
FROM master..sysprocesses
WHERE dbid=db_id(@DatabaseName)

Kill All Connections to a SQL Server database

Useful script to kill all connections for a given database. Use this if you are getting deadlocks or you have processes that are running too long. Use with caution.

Hope that helps, Jon Bosker, DB Gurus, 17 Nov 2015


USE master
GO


SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'MyDatabase' -- <<<<< -----change this------------
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END

SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
PRINT @spidstr
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

SQLServer All Table Counts: All Databases

Below is a script that gets all of the counts for all of the tables on a server. Pretty useful if you are looking for some rows to delete. Commented to explain how it works.

Hope that helps, Jon Bosker, DB Gurus, 17 Nov 2015

-- We are going to save the results on master
USE master 

-- Create the tables to stpore the results
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'dbgDatabases')
DROP TABLE dbgDatabases
GO
CREATE TABLE dbgDatabases
(
id int identity,
Databasename varchar(200),
)

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'dbgRowCounts')
DROP TABLE dbgRowCounts
GO
CREATE TABLE dbgRowCounts
(
Databasename varchar(200),
TableName varchar(200), 
NumberOfRows int,
DateStamp datetime
)

-- Get a list of all of the user databases
INSERT INTO dbgDatabases
Select [name] from Sys.Databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB','mail','WebsitePanel')

DECLARE @sSQL1 varchar(8000)
DECLARE @nCounter1 int
SET @nCounter1 = 1

-- Build the SQL
-- I got this SQL from http://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database
WHILE EXISTS(SELECT * FROM master.[dbo].[dbgDatabases] WHERE id >= @nCounter1)
BEGIN
SELECT  @sSQL1 = 'INSERT INTO master.[dbo].[dbgRowCounts] (Databasename, TableName, NumberOfRows, DateStamp) 
SELECT ''' + Databasename + ''', o.name, ddps.row_count, ''' + CAST(GETDATE() AS varchar) + '''
FROM [' + Databasename + '].sys.indexes AS i
INNER JOIN [' + Databasename + '].sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN [' + Databasename + '].sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id 
WHERE i.index_id < 2  AND o.is_ms_shipped = 0 ORDER BY o.NAME'
FROM master.[dbo].[dbgDatabases] 
WHERE id = @nCounter1
PRINT @sSQL1 
EXEC (@sSQL1)
SET @nCounter1 = @nCounter1+1
END

-- Show the results - I am showing biggest first
SELECT * FROM master.[dbo].[dbgRowCounts] ORDER BY NumberOfRows DESC