SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8/1024 AS UsedSpaceMB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB desc
T-SQL
List out the server permissions / securables of an user – TSQL
SET NOCOUNT ON;
DECLARE @Login sysname;
SET @Login = ‘login_name’;
SELECT CASE P.state WHEN ‘W’ THEN
P.permission_name + ‘ WITH GRANT OPTION;’
ELSE
P.state_desc + ‘ ‘ + P.permission_name
END AS ‘Server Permission’
FROM sys.server_permissions AS P
JOIN sys.server_principals AS L
ON P.grantee_principal_id = L.principal_id
WHERE L.name = @Login
AND P.class in (‘100′,’101′,’105’)
List out the server roles of an user – TSQL
SET NOCOUNT ON;
DECLARE @Login sysname;
SET @Login = ‘login_name’;
SELECT R.name AS ‘Server Roles’
FROM sys.server_role_members AS RM
JOIN sys.server_principals AS L
ON RM.member_principal_id = L.principal_id
JOIN sys.server_principals AS R
ON RM.role_principal_id = R.principal_id
WHERE L.name = @Login;
What permission should we grant to view an execution plan?
Apart form db_datareader we have to grant SHOWPLAN permission at the database level.
USE DatabaseName
Go
Go
Grant SHOWPLAN to [username]
Note: sysadmin, dbcreator or db_owner ‘s can see the execution plan with out showplan permission.
Default Trace in SQL Server
The default trace is a system-defined trace which resides in SQL Server directory ,which consists of 5 files with the size of 20 MB each one.
Below one is the script to find out the path of default trace where it is running or saved.
SELECT * FROM fn_trace_getinfo(default);
| traceid | property | value | |
| 1 | 1 | 2 | Configured trace options |
| 1 | 2 | C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\log_16.trc | Trace file name |
| 1 | 3 | 20 | Max file size for the *.trc file |
| 1 | 4 | NULL | Stop time for the trace session |
| 1 | 5 | 1 | Current trace status (1 = On and 0 = Off) |
After get the default trace path info, use the below query to fetch the trace information.
SELECT * FROM ::fn_trace_gettable(‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\log_16.trc’, default)
Query to find SQL Server IP address and client IP address T-SQL
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name] ,SERVERPROPERTY('InstanceName') AS [Instance Name] ,LOCAL_NET_ADDRESS AS [IP Address Of SQL Server],CLIENT_NET_ADDRESS AS [IP Address Of Client] FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPIDHow to read a SQL Profiler trace file in Management studio – SQL Server
fn_trace_gettable (‘filename’ , number_files )
number_files specifies the number of rollover files to be read.
SELECT *
FROM fn_trace_gettable(‘c:\inside_sql.trc’, 1)
Find all the objects that have been modified in last 7 days – SQL Server
USE Test_DB
GO
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() – 7 — no of days
ORDER BY modify_date;
GO
How to find Row count for all tables in a database – SQL Server
SELECT
QUOTENAME(SCHEMA_NAME(O.schema_id)) + ‘.’ + QUOTENAME(O.name) AS [TableName]
, SUM(P.Rows) AS [RowCount]
FROM
sys.objects AS O
INNER JOIN sys.partitions AS P ON O.object_id = P.object_id
WHERE
O.type = ‘U’ AND index_id < 2
GROUP BY
O.schema_id
, O.name
ORDER BY [TableName]
GO
—————————————————————–
Below script will give you index wise row count.
USE [Adventureworks];
GO
SELECT OBJECT_NAME([p].[object_id]) AS [Table] ,
[p].[index_id] AS [Index ID] ,
[i].[name] AS [Index] ,
[p].[rows] AS "Number of Rows"
FROM [sys].[partitions] AS [p]
JOIN [sys].[indexes] AS [i] ON [p].[object_id] = [i].[object_id]
AND [p].[index_id] = [i].[index_id]
WHERE [p].[object_id] = OBJECT_ID('dbo.sales');Get failed jobs report — SQL Server
——–Script to get the failed jobs report
BEGIN
SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY(‘ServerName’)) AS ServerName,
j.name AS job_name,
CASE jh.run_status WHEN 0 THEN ‘Failed’
WHEN 1 THEN ‘Succeeded’
WHEN 2 THEN ‘Retry’
WHEN 3 THEN ‘Canceled’
WHEN 4 THEN ‘In Progress’ ELSE
‘Status Unknown’ END AS ‘last_run_status’,jh.message as MessageDetails
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON
ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity)
and jh.run_status = 0 ORDER BY job_name
END
selectstarfromyogi 