SQL Server Useful Scripts

SQL Script makes DBA’s life easy. Many time we need to get the data or perform the operation using a script. Below are various scripts you can run to collect data.

SQL Server Useful Scripts
1. Script to Verify compatibility level and change the same if required.

--Verify Compatibility Level
SELECT name, compatibility_level, collation_name FROM sys.databases
GO
--Change Compatibility Level
USE [master]
GO
ALTER DATABASE [DB_Name] SET COMPATIBILITY_LEVEL = 110
GO

2. Script to Note down Linked Servers details.

--Verify Linked Servers
SELECT * FROM sys.sysservers
GO

3. Script to List Database Dependent Jobs

select 
distinct 
name,
database_name
from sysjobs sj
INNER JOIN sysjobsteps sjt on sj.job_id = sjt.job_id

4. Script to Note down recovery model details.

--Check Recovery Model
SELECT name, recovery_model_desc FROM sys.databases
WHERE name = 'DBName'
GO

5. Script to Note down collation setting.

--Verify Collation setting
SELECT name, collation_name FROM sys.databases
WHERE name = 'DBName';
GO

6. Script to Verify database properties and alter the same if required.

--Check Database Properties
SELECT *FROM sys.databases SD
JOIN sys.syslogins SL ON SD.owner_sid = SL.sid
GO

7. Script to Enable trustworthy database settings if required.

--Verify Trustworthy Settings
SELECT is_trustworthy_on FROM sys.databases WHERE name = 'DBName'
GO
--Enable Trustworthy Database Settings if required
ALTER DATABASE DBName SET TRUSTWORTHY ON
GO

8. Script to List Orphan Users

sp_change_users_login 'report'
GO

9. Script to Rebuild Indexes as per the requirement. (This will rebuild or reorganize all indexes for a particular table.)

• Index Rebuild :- This process drops the existing Index and Recreates the index.
• Index Reorganize :- This process physically reorganizes the leaf nodes of the index.

-- Script for Index Rebuild
USE [DBName];
GO
ALTER INDEX ALL ON [ObjectName] REBUILD
GO
-- Script for Index Reorganize
USE AdventureWorks;
GO
ALTER INDEX ALL ON [ObjectName] REORGANIZE
GO

10. Script to Check the Disk and Database Size

-- Procedure to check disc space
exec master..xp_fixeddrives
-- To Check database size
exec sp_helpdb [dbName]
or
use [dbName]
select str(sum(convert(dec(17,2),size)) / 128,10,2)+ 'MB'
from dbo.sysfiles
GO

11. Script to Check Database Properties

select 
sysDB.database_id,
sysDB.Name as 'Database Name',
syslogin.Name as 'DB Owner',
sysDB.state_desc,
sysDB.recovery_model_desc,
sysDB.collation_name, 
sysDB.user_access_desc,
sysDB.compatibility_level, 
sysDB.is_read_only,
sysDB.is_auto_close_on,
sysDB.is_auto_shrink_on,
sysDB.is_auto_create_stats_on,
sysDB.is_auto_update_stats_on,
sysDB.is_fulltext_enabled,
sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

12. Script to Refresh all the views of database.

--Refresh Views
USE DBName
EXEC sp_refreshview 'ViewName'
GO

13. Script to List Linked Servers

select *
from sys.sysservers

14. Script to List Database Dependent Jobs

select 
distinct 
name,
database_name
from sysjobs sj
INNER JOIN sysjobsteps sjt on sj.job_id = sjt.job_id

15. Script to Change the database to read-only mode

-- Script to make the database readonly
USE [master]
GO
ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [DBName] SET nbsp; READ_ONLY 
GO

16. Script to Change DB Owner

This will change the database owner to “sa”.  This can be used to change to any owner you would like.

USE databaseName
EXEC sp_changedbowner 'sa'

17. Script to Turn on Trustworthy Option

If trustworthy option was set, this will turn it on for the database.

ALTER DATABASE database_name SET TRUSTWORTHY ON

18. Script to Change the Database Compatibility Level (When you upgrade to a new version, the old compatibility level will remain. This script shows how to change the compatibility level to SQL Server 2005 compatibility ).

ALTER DATABASE DatabaseName
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel DatabaseName, 90;
GO
ALTER DATABASE DatabaseName
SET MULTI_USER
GO

19. Script to Check and Fix Orphan Users

-- Script to check the orphan user
EXEC sp_change_users_login 'Report'
--Use below code to fix the Orphan User issue
DECLARE @username varchar(25)
DECLARE fixusers CURSOR 
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers

20. Script to Update database tables statistics.

--Update Database Statistics
USE DBName
EXEC sp_updatestats
GO

21. Script to Correct all the pages and row counts with DBCC UPDATEUSAGE command.

--Correct Pages and Row Counts
DBCC UPDATEUSAGE('DBName') WITH COUNT_ROWS
GO

22. Check integrity of objects with DBCC CHECKDB command.

--Check Integrity of Objects
DBCC CHECKDB('DBName') WITH ALL_ERRORMSGS
GO

23. Script to Recompile all stored procedures, functions and triggers.

--Recompile Objects
USE DBName
EXEC sp_recompile 'ObjectName'
GO

 


<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.