Sep
List of all objects :
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.
List of Procedure :
Run following simple script on SQL Server 2005 to retrieve all stored procedure in database.
SELECT *
FROM sys.procedures;
This will ONLY work with SQL Server 2005.
List of all :
–Get the all Procedure List
SELECT *
FROM sys.procedures;
SELECT *
FROM sys.tables;
–
SELECT *
FROM sys.Views;
–
SELECT *
FROM sys.triggers;
–
Select * from sys.procedures where [type] = ‘P’ and is_ms_shipped = 0 and [name] not like ’sp[_]%diagram%’
–
SELECT name
FROM sys.objects
WHERE type = ‘P’
AND DATEDIFF(D,create_date, GETDATE()) < 7
–
select * from sys.objects where type=’S’ — SYSTEM_TABLE
select * from sys.objects where type=’U’ — USER_TABLE
select * from sys.objects where type=’P’ — SQL_STORED_PROCEDURE
select * from sys.objects where type=’FN’– SQL_SCALAR_FUNCTION
select * from sys.objects where type=’TF’– SQL_TABLE_VALUED_FUNCTION
select * from sys.objects where type=’IF’– SQL_INLINE_TABLE_VALUED_FUNCTION
select * from sys.objects where type=’V’ — VIEW
select * from sys.objects where type=’D’ — DEFAULT_CONSTRAINT
select * from sys.objects where type=’PK’– PRIMARY_KEY_CONSTRAINT
select * from sys.objects where type=’UQ’– UNIQUE_CONSTRAINT
select * from sys.objects where type=’F’ — FOREIGN_KEY_CONSTRAINT
select * from sys.objects where type=’SQ’– SERVICE_QUEUE
select * from sys.objects where type=’IT’– INTERNAL_TABLE
