Query to list all SA or sysadmin users in SQL Server

USE master
GO
SELECT p.name AS [loginname] , Role =
case s.sysadmin
when ‘1’ then ‘sysadmin’
end
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN (‘SQL_LOGIN’, ‘WINDOWS_LOGIN’, ‘WINDOWS_GROUP’)
— AND p.name NOT LIKE ‘##%’
AND s.sysadmin = 1

How to find linked server table structure using T-SQL – SQL Server

The following sp_columns_ex stored procedure used to return the linked server table structure details.

EXEC sp_columns_ex ‘linkedserver_Name’, ‘table_name’, ‘Schema_name’
EXEC sp_columns_ex ‘prod_lnk’, ’employee’, ‘Humaresources’

How to find table structure using T-SQL.

Use Database_name

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘Table_name’

Find duplicates values and count in a column – SQL Server 2012

Following query will help you to find out the duplicate values and their count in a column.

SELECT column_name, COUNT(*) Duplicates_Count
FROM [dbo].[table_name]
GROUP BY column_name

Find Buffer cache hit ratio value using TSQL ? – SQL server 2012

— Below script will help you to find the Buffer cache hit ratio value in %.

–Method 1:

SELECT [object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Manager%’
AND [counter_name] = ‘Page life expectancy’

 

—Method 2:

DECLARE @Buffer_cache_hit_ratio INT;
DECLARE @Buffer_cache_hit_ratio_base INT;
SELECT @Buffer_cache_hit_ratio = cntr_value FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’ AND [counter_name] = ‘Buffer cache hit ratio’
SELECT @Buffer_cache_hit_ratio_base = cntr_value FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’ AND [counter_name] = ‘Buffer cache hit ratio base’
select 100*@Buffer_cache_hit_ratio/@Buffer_cache_hit_ratio_base as ‘Buffer_cache_hit_ratio_value’

—-Method 3:

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Buffer cache hit ratio base’
AND OBJECT_NAME = ‘SQLServer:Buffer Manager’) b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = ‘Buffer cache hit ratio’
AND a.OBJECT_NAME = ‘SQLServer:Buffer Manager’

 

 

Attach database using T-SQL – SQL Server

1)EXEC sp_attach_db @dbname = N'AdventureWorks2012', 
    @filename1 = 
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf', 
    @filename2 = 
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_log.ldf';


2) USE master;
GO
CREATE DATABASE MyAdventureWorks 
ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),
(FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')
FOR ATTACH;
GO

T-SQL to find whether the SQL server is running on physical or virtual server.

SELECT SERVERPROPERTY(‘computernamephysicalnetbios’) AS ServerName
,Server_type = CASE
WHEN virtual_machine_type = 1
THEN ‘Virtual’
ELSE ‘Physical’
END
FROM sys.dm_os_sys_info

DMV’s to find Certificates and Symmetric keys on a Database – SQL Server

use Database
select * from sys.certificates
Go
select * from sys.symmetric_keys

SELECT * FROM SYS. KEY_ENCRYPTIONS;

T-SQL to find out the database with the object name – SQL server

EXECUTE master.sys.sp_MSforeachdb ‘USE [?];select “?” as databasename; select * from sys.objects where name like “startup” ‘

How to restart endpoints in database mirroring – SQL server

USE MASTER
GO
ALTER ENDPOINT Mirroring_endpointname STATE = STOPPED;
GO
ALTER ENDPOINT Mirroring_endpointname  STATE = STARTED;
GO