I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 7 days. If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If stored procedure was created but never modified afterwards modified date and create date for that stored procedure are same.
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
----Change 7 to any other day value
Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7
----Change 7 to any other day value.
Date condition in above script can be adjusted to retrieve required data.
Reference : Pinal Dave (https://blog.sqlauthority.com)
84 Comments. Leave new
can you show also the host name of the machine who last modify it?
I dont think it is possible until you had run a profiler
I think it is possible by using ddl trigger on database
Hi
Is there any way to find out who has modified the stored procdure
Srinivas
As I said in my previous reply, you should have run a profiler to find this
Hi Madhivanan,
I am very new to this. How can I run a profiler?
Refer this
https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ff650699(v=pandp.10)
Hi Madhivanan,
My SPROC got modified about a week back, I won’t be able to track the user name who modified the SPROC right?
Is there any way? My understanding is, it cannot be found out even using Profiler?
Thanks,
Srinivas
There’s an error in your queries.
The line:
AND DATEDIFF(D,modify_date, GETDATE()) > 7
should be:
AND DATEDIFF(D,modify_date, GETDATE()) < 7
Same with the create_date query. Your query checks modifications/creations MORE than 7 days old.
Beyond finding out about procs changed, when; how do I tell the last date, value and who modified a database setting such as “Read_Committed_Snapshot”?
I know I can test to see the current value with “is_read_committed_snapshot_on” from sys.databases but I need to know when the setting was changed and what value/whom set this type of property?
How much info can I get? how?
What events in SQL such as Update Statistics could turn this setting off without user intervention or user knowledge? How, separately perhaps, would I also audit such events/changes?
Thanks
Thank you for the query…
I need to add serial number to the record extracted from SQL server 2000. Is there any way to have my own set of serial number generated along with the query.
ex:
1 suresh 23
2 samuel 39
or
Please suggest….
If you want to show data in the front end application, do numbering there. Otherwise copy the resultset a temp table that has identity column
Thank you for the query…
I need to add serial number to the record extracted from SQL server 2000. Is there any way to have my own set of serial number generated along with the query.
ex:
1 suresh 23
2 samuel 39
or
Please suggest….
Select Row_number() Over(order by id)SerialNumber,*, From Table Name
Note that in version 2000, you can’t use row_number() function
Hi Suresh
Please try this query
Select (ROW_NUMBER() OVER ( Order By Height)) as MyRowNumber , Height, CorrectedHeight from Results
// Gandhi
Skalar –
Agaram Instruments
Chennai
THATS Good One thanks
Thanks Pinal
good!
thanks , very helpful ^^
is there a query to know the person who has made the change.. ie, if there has been a change in the sp / view / table, how to find who has done that change.
This is old thread, but you could make database level triggers to audit DDL changes.
and what to display tables?
very nice. I got my doubts cleared through this.thanks a lot
Thank you so much, viewing the records for the last 7 days was the last task to complete my online application and I just want to say thanks for making it so easy.
Hi PinalDave,
i have find an interesting proc on web for some related task that you are doing here, here is location:
and after that you just call it whit something like:
use db_name
execute sp_ListObjects ‘P’
What do you think about this..?
Greatings, k
to list out all the month in a year in Sql Server
select datename(month,dates) as month_name from
(
select dateadd(month,number,0) as dates from master..spt_values where type=’p’ and number between 0 and 11
) as t
Hi ,
Is there any SQLquery to find out how many tables are created today?
Thanks in advance.
select name from sysobjects
where
crdate>=dateadd(day,datediff(day,0,getdate()),0) and
crdate<dateadd(day,datediff(day,0,getdate())+1,0)
thanks great post
By MK Gandhi
to add srno in retrieved records using querry
do this for sql server 2000 for same result :
select *
from information_schema.routines where Routine_Type=’procedure’
AND DATEDIFF(D,last_altered, GETDATE()) <7
select @@databasename
if you want to find modified sp in the specific database
add the below conditionL
and specific_Catalog=’yourrdatabasename’