Occasionally, there comes a requirement to search all databases on a particular server for either columns with a specific name, or columns relating to a specific subject. In the most recent case, I had to find all similar columns in all databases because the company plans to change the datatype of these columns. My task [...]
Archive of posts filed under the Scripts category.
Is Job Running?
The following SQL Server script works to determine if a particular job is currently running. This feature is nice to have if you do not want jobs to step over one another. I frequently will use this script as the first step of the job. If it is currently running, then the step will raise [...]
Kill All Connections on a Database
This seems to be the easiest way to kill all connections (sessions) on a SQL Server database: ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE After executing the above, it will put the database in single user mode. So you then need to remove single user mode using: EXEC sp_dboption ‘database_name’, ‘single user’, ‘FALSE’
Rebuild and Reorganize Fragmented Indexes
This script will automatically determine whether a rebuild or a reorganize should be used according to the fragmentation of the index. It will then execute the appropriate command. Note that performing index rebuilds online during production hours will cause contention. SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(258); [...]
How to Update Statistics
There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table. EXEC sp_updatestats The other way, is to use the UPDATE STATISTICS command. This command gives much [...]
Rowcount for All Tables
SELECT SUM(rowcnt)
FROM sysindexes
WHERE indid < 2
Blocking Processes (lead blocker)
SELECT
spid
,status
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(char(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
Create Date Table
create table dates(
id int not null,
[date] as (yyyy+mm+dd),
yyyy char(4) not null,
mm char(2) not null,
Find Missing Table and Index Statistics
SELECT
Last_Updated = STATS_DATE(si.id, si.indid)
,TableName = object_name(si.id)
,Name = RTRIM(si.name)
,Size = DATALENGTH (si.statblob)
FROM
sysindexes si WITH (nolock)
WHERE OBJECTPROPERTY(si.id, N’IsUserTable’) = 1
–AND INDEXPROPERTY (si.id , si.name , ‘IsAutoStatistics’ ) = 0
order by last_updated, tablename
Reindex All Tables in a Database
Code for reindexing all tables in a database using dbreindex
