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
hi everyone ,
how can i list out the table names from the stored procedure in sql 2005….,
Use this
exec sp_depends ‘procedure name’
Thank you for for the tip, it is useful when we make the new release.
thanks…
Thank you! It is a great post. I’d like to clarify one thing. Both queries can return the same values. This can happen if I created a new stored procedure within the last 7 days and then updated it.
Thanks for your comments. in several oportunities they were very usefull for me.
Thanks from Colombia – South América
Hi Pinal
I need to generate the list of all tables whose structures were modified in the last 15 days. All tables whose data were modified should not be included in the list.
Hi.. Thanks for all the info.. I have a query in my environment.. please let me know hw to achieve the below agenda :
-> Monitor the environment and find if a new sql agent job has been created. If yes report the details of the job; We can catch the new jobs and update the job with notifications assigned to person/DL responsible for the job.
-> Monitors the environment and find status of Server level priviliges of all the logins and sends a report. We can identify any changes in the priviliges of logins by comparing with the previous report
Please help me.. Its urgent..
Hi Pinal,
Is there a way to find out who (which domain user) has created a login on the server? And who (which domain user) has modified the same login ? if they have modified, what have they edited, old value and new values to be specific ?
select * from master.sys.syslogins — shows the createdate, updatedate and accdate but not the user info.
sys.fn_tracegettable also has a limitation of only 5 traces.
Can you help me ?
Nitin.
I need to build an sql of all users who have logged the last three months. I’ve trying to understand system sps for example whoisactive, but I dont undertand them. Any idea please.
thanks a lot
How to see who changed sprocs?
Thank You So much.. Its a timely help for my project
i need program for dispaly the date*dayname of the one week using sql procedure can u help me
Thank you so much.. Helped
how to compare multiple date attributes with current date
We are trying to create a stored procedure to archive data older than 6 months (180 days) from our production database in to a new archive database.
We also want to delete those archived rows from the production database.
We are thinking to include a while loop, but we want to archive only 1,000 rows a day and we need to schedule it on daily basis.
Can you please share us your experience – ASAP
Thanks
1000 rows a day? What’s the logic behind that?
Hi Pinal,
Daily above 1000 records insert into production database, when i scheduled job, i want 6 months older than records go to archive table. remaining records are existing in production table. currently we are maintain 6 months records only its enough. other records are we are maintain archive table.
Thanks
Run on all databases
DECLARE @command varchar (1000)
SELECT @command = ‘use ? SELECT ”?” DBname,name,modify_date
FROM sys.objects
WHERE type = ”P”
AND DATEDIFF(D,modify_date, GETDATE()) < 150'
EXEC sp_MSforeachdb @command
Hi Pinal Dave,
I have a requirement like trying find the script for alter the Stored procedure and take the backup of existing SP in development server and that should be automated or just script anything is fine.
Every time if any changes on SP’s in Development Server those data not be reflected in Prod server.
So is there any other solution for solving the issue.
Please Help with this.