----------------------------------------------------------------
-- 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
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
No comments:
Post a Comment