how to find changes in database

  By Junaid A   Posted on January-02-2023   93

MS Sql Server

use testshop2
go
-- how to find changes in database | find modifications in database

Declare @kw nvarchar(500)='a%'

--Query to search last executed sp's in db
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC


-- Query to search through Stored procedures
SELECT o.type_desc AS ROUTINE_TYPE,o.[name] AS ROUTINE_NAME,
m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m INNER JOIN sys.objects AS o
ON m.object_id = o.object_id WHERE m.definition LIKE @kw 
order by ROUTINE_NAME


-- Query to Find Column From All Tables of Database
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where c.name like @kw
ORDER BY schema_name, table_name;

use testshop2
go
-- how to find changes in database | find modifications in database

Declare @kw nvarchar(500)='a%'

--Query to search last executed sp's in db
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC


-- Query to search through Stored procedures
SELECT o.type_desc AS ROUTINE_TYPE,o.[name] AS ROUTINE_NAME,
m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m INNER JOIN sys.objects AS o
ON m.object_id = o.object_id WHERE m.definition LIKE @kw 
order by ROUTINE_NAME


-- Query to Find Column From All Tables of Database
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where c.name like @kw
ORDER BY schema_name, table_name;

By  asifjans    02-Jan-2023 Views  93



You may also read following recent Post