SQL Server Frequently Used Procedures and Functions

Use DataBase
Go
Sp_helpuser   -- Displays complete user details along with DB roles of the current database

Object_Name (‘Object_Id’) – Returns name of the object

DB_Name (‘DBID’) – Returns name of the database

Schema_Name(‘Schema_ID’) – Returns name of the schema

Select @@servername -- Returns ServerName

Sp_helpdb <database_name> -- Returns information about the given database.

Sp_helpserver – Displays current server details

SELECT SERVERPROPERTY('Edition') – Displays current SQL Server edition

SELECT SERVERPROPERTY(‘ProductLevel') – Displays current SQL Server Product Level

SELECT SERVERPROPERTY(‘ProductVersion') – Displays current SQL Server Product Version

SELECT SERVERPROPERTY(‘ServerName') – Displays current SQL Server Product Version

SELECT 'kill ' + CAST(spid AS VARCHAR) + '' from sys.sysprocesses where db_name(dbid)
='msdb'  -- Kill connections for a particular database.

Alter Database database_name set single_user with rollback immediate – To keep database in a
single user mode.

Alter Database database_name set multi_user  -- To keep database in multi user mode.

Use <db_name>
go
Dbcc open_tran  -- Displays are there any open transactions in the current database.

Select * from sys.sysprocesses where open_tran>0 – displays open transactions from all the databases on the server.

Sp_who 2  -- Displays list of processes that are currently present in the server.

To rename SQL Server
Sp_dropserver ‘servername’
Sp_addserver ‘newname’,local
Restart the sql service
To know the dependencies of an object
SELECT * FROM sys.dm_sql_referenced_entities('dbo.uspGetManagerEmployees','object')

Sp_depends ‘stored_proc’ – It doesn’t give accurate information and it also deprecated.