List tables in a database

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

List views in a database

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='VIEW'

Count number of rows for all tables in a database

SELECT DISTINCT SCH.name AS SchemaName  
      ,OBJ.name AS TableName    
      ,STATS.row_count AS [RowCount]
FROM sys.partitions AS PART  
	INNER JOIN sys.dm_db_partition_stats AS STATS  
         ON  STATS.partition_id = PART.partition_id
            AND STATS.partition_number = PART.partition_number
     INNER JOIN sys.objects AS OBJ  
         ON OBJ.object_id = STATS.object_id
     INNER JOIN sys.schemas AS SCH  
         ON  SCH.schema_id = OBJ.schema_id
WHERE SCH.name <> 'sys'
ORDER BY SchemaName, TableName

Which tables in the AdventureWorks2019 database have column name like ‘EmployeeID’?

USE AdventureWorks2019;

SELECT t.name AS 'Table Name',
SCHEMA_NAME(schema_id) AS 'Schema Name',
c.name AS 'Column Name'
FROM sys.tables t
JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY 'Schema Name', 'Table Name';

Describe a table

exec sp_columns 'table_name'

How can I list all foreign keys referencing a given table?

exec sp_columns 'table_name'

How to kill all connections to a database?

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Your DB Name')

EXEC(@kill);

How do you import a large MS SQL .sql file?

You can use the command line below from sqlcmd utility.

sqlcmd -S <SERVER> -i <YOUR FULL FILE PATH HERE>

List jobs and their job owners

SELECT j.name AS 'Job Name', l.name AS 'Job Owner'
FROM msdb..sysjobs j
LEFT JOIN master.sys.syslogins l ON j.owner_sid = l.sid
ORDER by l.name

List job statuses and schedule statuses

SELECT
       j.name AS 'Job Name',
       j.enabled AS 'Job Status',
       CASE WHEN j.enabled = 1 THEN 'Enabled' 
            ELSE 'Disabled' 
       END AS 'Job Status Desc',
       s.name AS 'Schedule Name',
       s.enabled AS 'Schedule Status',
       CASE WHEN s.enabled is null THEN 'N/A'
            WHEN s.enabled = 1 THEN 'Enabled'
            ELSE 'Disabled'
       END AS 'Schedule Status Desc'
FROM msdb.dbo.sysjobs j
left join msdb.dbo.sysjobschedules js on j.job_id = js.job_id 
left join msdb.dbo.sysschedules s on s.schedule_id = js.schedule_id
ORDER BY j.name

Check if the SQL server database is SSL enabled

SELECT session_id, encrypt_option FROM sys.dm_exec_connections

Check last time the table was updated

SELECT OBJECT_NAME(OBJECT_ID) AS TableName,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'Database Name')
AND OBJECT_NAME(OBJECT_ID) like '%Table Name%'

Check last time a stored procedure was executed

SELECT o.name,
       s.last_execution_time,
       s.type_desc,
       s.execution_count, *
FROM sys.dm_exec_procedure_stats s
INNER JOIN sys.objects o ON s.object_id = o.object_id
WHERE DB_NAME(s.database_ID) = 'Database Name'
AND o.name like ('%Stored procedure name%')

Get the last ID inserted in a table

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()