Generate and change all SQL databases to a different compatibility level - Alter SQL Compatibility Level

SELECT name, compatibility_level  

FROM sys.databases where compatibility_level not in(120) -- SQL Server 2012 compatibility_level is 120

---------------------------------

SELECT name, compatibility_level  

FROM sys.databases 

---------------------------

/* Start Of Code */

DECLARE @SQL VARCHAR(max)  = ''

,             @CompLevel int = 120 -- Need to change the compatibility_level as per SQL Server version

SELECT @SQL += 'ALTER DATABASE ' + quotename(NAME) + ' SET COMPATIBILITY_LEVEL = ' + cast(@CompLevel as char (3)) + ';' + CHAR(10) + CHAR(13)

FROM sys.databases

WHERE 

--COMPATIBILITY_LEVEL not in(120) 

--and 

 compatibility_level <> @CompLevel

PRINT @SQL

--EXEC (@SQL)

/* End Of Code */

Finding an object in server in SQL Server using SP_MSFOREACHDB

 Today one of my friend asked me that she has created a stored procedure in a database, suddenly she has recognized she has forgotten the database in which the procedure was created. We suggested him to use the below query which searches each and every database in the server and returns the database name and the object name that is specified in where clause.

SP_MSFOREACHDB searches the objects in all databases in the server.

Example:

SP_MSFOREACHDB

'

USE ?

select

    ''?'' Database_Name

   , Name Object_name

from sys.procedures

WHERE name LIKE ''USP_Test''

Using SQL Server script to get list of users and their database roles in SQL Server

use Database_Name;

go 

SELECT  dPrinc.name AS [Members],dRole.name AS [Database Role Name]

FROM sys.database_role_members AS dRo  

JOIN sys.database_principals AS dPrinc  

    ON dRo.member_principal_id = dPrinc.principal_id  

JOIN sys.database_principals AS dRole  

    ON dRo.role_principal_id = dRole.principal_id;