----------------------------------------------------------------
-- Find currently running jobs
SELECT
j.name AS JobName,
ja.job_id,
ja.start_execution_date,
ja.session_id,
DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS RunningMinutes
FROM msdb.dbo.sysjobactivity ja
INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.stop_execution_date IS NULL
AND ja.start_execution_date IS NOT NULL
----------------------------------------------------------------
-- Get detailed job execution information
SELECT
j.name AS JobName,
j.job_id,
ja.start_execution_date,
ja.session_id,
CASE ja.last_executed_step_id
WHEN 0 THEN 'Job is currently running'
ELSE 'Job completed or failed'
END AS Status
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobactivity ja ON j.job_id = ja.job_id
WHERE ja.session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity
WHERE job_id = j.job_id
)
AND ja.stop_execution_date IS NULL
----------------------------------------------------------------
-- Stop a job by name
EXEC msdb.dbo.sp_stop_job @job_name = 'YourJobName'
----------------------------------------------------------------
-- Stop a job by job_id
EXEC msdb.dbo.sp_stop_job @job_id = 'Your-Job-ID-Here'
----------------------------------------------------------------
-- Stop all currently running jobs
DECLARE @JobName NVARCHAR(128)
DECLARE job_cursor CURSOR FOR
SELECT j.name
FROM msdb.dbo.sysjobactivity ja
INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.stop_execution_date IS NULL
AND ja.start_execution_date IS NOT NULL
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_stop_job @job_name = @JobName
PRINT 'Stopped job: ' + @JobName
FETCH NEXT FROM job_cursor INTO @JobName
END
CLOSE job_cursor
DEALLOCATE job_cursor
----------------------------------------------------------------
--Here both source and target table exist
INSERT
INTO TargetTbl
SELECT * FROM SourceTbl
----------------------------------------------------------------
--Here source table exist, and target table not exist
SELECT *
INTO TargetTbl
FROM SourceTbl;
----------------------------------------------------------------
--Find Database Routines Using Custom Table Type
--This query searches for stored procedures, functions, and other database routines
that reference a specific user-defined table type in their definition.
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%MyTableType%';
----------------------------------------------------------------
--Find Object Dependencies Using sp_depends
--This system stored procedure identifies all database objects that depend on or are
depended upon by the specified table.
EXEC sp_depends 'MyTable1';
----------------------------------------------------------------
--Database Schema Analysis Query - Complete Table and Column Metadata with Constraints
--Purpose: Retrieves comprehensive metadata for all database tables and views,
including detailed column information and constraint relationships.
SELECT
t.TABLE_SCHEMA, t.TABLE_TYPE, t.TABLE_NAME,
c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE, c.COLUMN_DEFAULT,
kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON c.TABLE_NAME = kcu.TABLE_NAME
AND c.COLUMN_NAME = kcu.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
ORDER BY t.TABLE_SCHEMA,t.TABLE_TYPE, t.TABLE_NAME, c.ORDINAL_POSITION
----------------------------------------------------------------
--Database Schema Analysis Query - Retrieves complete metadata for all database
objects (tables, functions, procedures, table types, views) with columns and
constraints in specified order
-- Get all tables and their columns with constraints
SELECT
'TABLE' as ObjectType,
1 as ObjectTypeOrder,
t.TABLE_SCHEMA as SchemaName,
t.TABLE_NAME as ObjectName,
c.COLUMN_NAME as ColumnName,
c.DATA_TYPE as DataType,
c.IS_NULLABLE,
c.COLUMN_DEFAULT,
c.ORDINAL_POSITION,
kcu.CONSTRAINT_NAME,
tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.TABLE_NAME = kcu.TABLE_NAME
AND c.COLUMN_NAME = kcu.COLUMN_NAME
AND c.TABLE_SCHEMA = kcu.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
WHERE t.TABLE_TYPE = 'BASE TABLE'
UNION ALL
-- Get functions and their parameters
SELECT
'FUNCTION' as ObjectType,
2 as ObjectTypeOrder,
r.ROUTINE_SCHEMA as SchemaName,
r.ROUTINE_NAME as ObjectName,
ISNULL(p.PARAMETER_NAME, 'NO_PARAMETERS') as ColumnName,
ISNULL(p.DATA_TYPE, 'N/A') as DataType,
'YES' as IS_NULLABLE,
NULL as COLUMN_DEFAULT,
ISNULL(p.ORDINAL_POSITION, 0) as ORDINAL_POSITION,
NULL as CONSTRAINT_NAME,
NULL as CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.ROUTINES r
LEFT JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.ROUTINE_NAME = p.SPECIFIC_NAME
AND r.ROUTINE_SCHEMA = p.SPECIFIC_SCHEMA
WHERE r.ROUTINE_TYPE = 'FUNCTION'
UNION ALL
-- Get stored procedures and their parameters
SELECT
'PROCEDURE' as ObjectType,
3 as ObjectTypeOrder,
r.ROUTINE_SCHEMA as SchemaName,
r.ROUTINE_NAME as ObjectName,
ISNULL(p.PARAMETER_NAME, 'NO_PARAMETERS') as ColumnName,
ISNULL(p.DATA_TYPE, 'N/A') as DataType,
'YES' as IS_NULLABLE,
NULL as COLUMN_DEFAULT,
ISNULL(p.ORDINAL_POSITION, 0) as ORDINAL_POSITION,
NULL as CONSTRAINT_NAME,
NULL as CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.ROUTINES r
LEFT JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.ROUTINE_NAME = p.SPECIFIC_NAME
AND r.ROUTINE_SCHEMA = p.SPECIFIC_SCHEMA
WHERE r.ROUTINE_TYPE = 'PROCEDURE'
UNION ALL
-- Get user-defined table types and their columns
SELECT
'TABLE_TYPE' as ObjectType,
4 as ObjectTypeOrder,
SCHEMA_NAME(tt.schema_id) as SchemaName,
tt.name as ObjectName,
c.name as ColumnName,
TYPE_NAME(c.system_type_id) as DataType,
CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE 'NO' END as IS_NULLABLE,
CASE
WHEN c.default_object_id > 0 THEN
(SELECT definition FROM sys.default_constraints WHERE object_id = c.default_object_id)
ELSE NULL
END as COLUMN_DEFAULT,
c.column_id as ORDINAL_POSITION,
NULL as CONSTRAINT_NAME,
NULL as CONSTRAINT_TYPE
FROM sys.table_types tt
JOIN sys.columns c ON tt.type_table_object_id = c.object_id
WHERE tt.is_user_defined = 1
UNION ALL
-- Get all views and their columns with constraints
SELECT
'VIEW' as ObjectType,
5 as ObjectTypeOrder,
t.TABLE_SCHEMA as SchemaName,
t.TABLE_NAME as ObjectName,
c.COLUMN_NAME as ColumnName,
c.DATA_TYPE as DataType,
c.IS_NULLABLE,
c.COLUMN_DEFAULT,
c.ORDINAL_POSITION,
kcu.CONSTRAINT_NAME,
tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.TABLE_NAME = kcu.TABLE_NAME
AND c.COLUMN_NAME = kcu.COLUMN_NAME
AND c.TABLE_SCHEMA = kcu.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
WHERE t.TABLE_TYPE = 'VIEW'
ORDER BY
ObjectTypeOrder,
SchemaName,
ObjectName,
ORDINAL_POSITION
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
I truly enjoyed reading articles on this platform qpoenergy as they are both informative and engaging. The website design is user-friendly, making navigation simple. It’s a great place to learn and stay updated on important topics.
ReplyDeleteSuch an excellent platform for knowledge techcutmedia delivering insightful and useful articles. The content is engaging and easy to follow, which makes it enjoyable to read. I appreciate the consistency and effort behind every post published here.
ReplyDelete