GREP https://phoenixnap.com/kb/grep-command-linux-unix-examples#:~:text=Grep%20is%20a%20Linux%20%2F%20Unix,searching%20through%20large%20log%20files.
SQL Server – Find running queries and kill them
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.start_time,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext ;
Kill session_id SQL Server DATE FORMATTING
| DATE ONLY FORMATS | ||
| Format # | Query | Sample |
|---|---|---|
| 1 | select convert(varchar, getdate(), 1) | 12/30/06 |
| 2 | select convert(varchar, getdate(), 2) | 06.12.30 |
| 3 | select convert(varchar, getdate(), 3) | 30/12/06 |
| 4 | select convert(varchar, getdate(), 4) | 30.12.06 |
| 5 | select convert(varchar, getdate(), 5) | 30-12-06 |
| 6 | select convert(varchar, getdate(), 6) | 30 Dec 06 |
| 7 | select convert(varchar, getdate(), 7) | Dec 30, 06 |
| 10 | select convert(varchar, getdate(), 10) | 12-30-06 |
| 11 | select convert(varchar, getdate(), 11) | 06/12/30 |
| 12 | select convert(varchar, getdate(), 12) | 061230 |
| 23 | select convert(varchar, getdate(), 23) | 2006-12-30 |
| 101 | select convert(varchar, getdate(), 101) | 12/30/2006 |
| 102 | select convert(varchar, getdate(), 102) | 2006.12.30 |
| 103 | select convert(varchar, getdate(), 103) | 30/12/2006 |
| 104 | select convert(varchar, getdate(), 104) | 30.12.2006 |
| 105 | select convert(varchar, getdate(), 105) | 30-12-2006 |
| 106 | select convert(varchar, getdate(), 106) | 30 Dec 2006 |
| 107 | select convert(varchar, getdate(), 107) | Dec 30, 2006 |
| 110 | select convert(varchar, getdate(), 110) | 12-30-2006 |
| 111 | select convert(varchar, getdate(), 111) | 2006/12/30 |
| 112 | select convert(varchar, getdate(), 112) | 20061230 |
| TIME ONLY FORMATS | ||
| 8 | select convert(varchar, getdate(), 8) | 00:38:54 |
| 14 | select convert(varchar, getdate(), 14) | 00:38:54:840 |
| 24 | select convert(varchar, getdate(), 24) | 00:38:54 |
| 108 | select convert(varchar, getdate(), 108) | 00:38:54 |
| 114 | select convert(varchar, getdate(), 114) | 00:38:54:840 |
| DATE & TIME FORMATS | ||
| 0 | select convert(varchar, getdate(), 0) | Dec 12 2006 12:38AM |
| 9 | select convert(varchar, getdate(), 9) | Dec 30 2006 12:38:54:840AM |
| 13 | select convert(varchar, getdate(), 13) | 30 Dec 2006 00:38:54:840AM |
| 20 | select convert(varchar, getdate(), 20) | 2006-12-30 00:38:54 |
| 21 | select convert(varchar, getdate(), 21) | 2006-12-30 00:38:54.840 |
| 22 | select convert(varchar, getdate(), 22) | 12/30/06 12:38:54 AM |
| 25 | select convert(varchar, getdate(), 25) | 2006-12-30 00:38:54.840 |
| 100 | select convert(varchar, getdate(), 100) | Dec 30 2006 12:38AM |
| 109 | select convert(varchar, getdate(), 109) | Dec 30 2006 12:38:54:840AM |
| 113 | select convert(varchar, getdate(), 113) | 30 Dec 2006 00:38:54:840 |
| 120 | select convert(varchar, getdate(), 120) | 2006-12-30 00:38:54 |
| 121 | select convert(varchar, getdate(), 121) | 2006-12-30 00:38:54.840 |
| 126 | select convert(varchar, getdate(), 126) | 2006-12-30T00:38:54.840 |
| 127 | select convert(varchar, getdate(), 127) | 2006-12-30T00:38:54.840 |
| ISLAMIC CALENDAR DATES | ||
| 130 | select convert(nvarchar, getdate(), 130) |  |
| 131 | select convert(nvarchar, getdate(), 131) | 10/12/1427 12:38:54:840AM |
MONTH – YEAR ONLY
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, <dateField>), 0) AS [year_month_date_field]
FROM
<your_table>
SQL Server – View tables last access time
select [schema_name],
table_name,
max(last_access) as last_access
from(
select schema_name(schema_id) as schema_name,
name as table_name,
(select max(last_access)
from (values(last_user_seek),
(last_user_scan),
(last_user_lookup),
(last_user_update)) as tmp(last_access))
as last_access
from sys.dm_db_index_usage_stats sta
join sys.objects obj
on obj.object_id = sta.object_id
and obj.type = 'U'
and sta.database_id = DB_ID()
) usage
group by schema_name,
table_name
order by last_access desc;
Columns
- schema_name – name of the schema
- table_name – name of the table
- LastAccess – last access time to table
Rows
- One row represents one table in database
- Scope of rows: all tables that was accessed in current SQL Server uptime
- Ordered by last access time
SQL Server – View Indexes
select schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type],
i.index_id,
case when i.is_primary_key = 1 then 'Primary key'
when i.is_unique = 1 then 'Unique'
else 'Not unique' end as [type],
i.[name] as index_name,
substring(column_names, 1, len(column_names)-1) as [columns],
case when i.[type] = 1 then 'Clustered index'
when i.[type] = 2 then 'Nonclustered unique index'
when i.[type] = 3 then 'XML index'
when i.[type] = 4 then 'Spatial index'
when i.[type] = 5 then 'Clustered columnstore index'
when i.[type] = 6 then 'Nonclustered columnstore index'
when i.[type] = 7 then 'Nonclustered hash index'
end as index_type
from sys.objects t
inner join sys.indexes i
on t.object_id = i.object_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0
order by schema_name(t.schema_id) + '.' + t.[name], i.index_id
Columns
- table_view – name of table or view index is defined for
- object_type – type of object that index is defined for:
- Table
- View
- index_id – id of index (unique in table)
- type
- Primary key
- Unique
- Not unique
- index_name – index name
- columns – list of index columns separated with “,”
- index_type – index type:
- Clustered index
- Nonclustered unique index
- XML index
- Spatial index
- Clustered columnstore index
- Nonclustered columnstore index
- Nonclustered hash index
Rows
- One row represents represents index
- Scope of rows: all indexes (unique and non unique) in databases
- Ordered by schema, table name, index id
Locks/ Long running queries in Azure SQL
SELECT TOP 10 r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st WHERE r.blocking_session_id = 0 and r.session_id in (SELECT distinct(blocking_session_id) FROM sys.dm_exec_requests) GROUP BY r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text ORDER BY r.total_elapsed_time desc
Search for a column name in SQL Server
SELECT c.name AS 'ColumnName'
,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%PushNetSales%'
ORDER BY TableName
,ColumnName;
/
SELECT COLUMN_NAME AS 'ColumnName'
,TABLE_NAME AS 'TableName'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%PushNetSales%'
ORDER BY TableName
,ColumnName;
List all tables in a Database ordered by record count – MSSQL
Method 1 – Using Stored Procedure
CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT "?", COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY row_count DESC
DROP TABLE #countsMethod 2 : Without stored procedure (eg : Azure SQL)
SELECT t.name, s.row_count from sys.tables t
JOIN sys.dm_db_partition_stats s
ON t.object_id = s.object_id
AND t.type_desc = ‘USER_TABLE’
AND t.name not like ‘%dss%’
AND s.index_id IN (0,1) order by row_count desc
Big Data 