SQL_Utility_StoredProcs

TO FIND STORED PROCEDURE CREATED WITHIN CERTAIN TIME RANGE
------------------------------------------------------------------
SELECT name
FROM sys.objects
WHERE type = 'P'
--AND DATEDIFF(D,create_date, GETDATE()) < 2
AND DATEDIFF(D,modify_date, GETDATE()) < 2
-------------------------------------------------------------------

TO FIND TABLES CREATED/MODIFIED WITHIN CERTAIN TIME RANGE

SELECT [name],create_date,modify_date FROM sys.Tables
WHERE modify_date > DATEADD(day,-2,GETDATE())

----------
SELECT name
FROM sys.objects
WHERE DATEDIFF(D,modify_date, GETDATE()) < 2
ORDER BY NAME ASC


-------------------------------------------------------------------

TO FIND TABLES/VIEWS CREATED/MODIFY WITHIN CERTAIN TIME RANGE
-------------------------------------------------------------------
SELECT [name],create_date,modify_date FROM sys.tables
order by modify_date desc

----------------------
SELECT [name],create_date,modify_date FROM sys.views
WHERE modify_date > DATEADD(day,-30,GETDATE())
-------------------------------------------------------------------

TO FIND ALL REFERENCES TO A COLUMN IN STORED PROCUDURES>
FINDING STORED PROCEDURES THAT REFERENCE\UPDATE A COLUMN
---------------------------------------------------------------------
select object_name(id)
from sysdepends
where depid = object_id('tbl_Directory')
and col_name(depid, depnumber) = 'IsRegistered'
order by 1
-----------------------------------------------------------------------


TO FIND TOTAL STORED PROCEDURE COUNT IN A DATABASE
----------------------------
select count(1) from sys.objects where type='P'
-------------------------------------------------------------------------


TO FIND STORED PROCS RELATED TO A TABLE IN DATABASE
SEARCH IN ALL STORED PROCEDURES
-----------------------------------

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tbl_Territory%'

----------------------------------------------------------------------


TO FIND A STORED PROCEDURE CONTAINING SOME TEXT OR COLUMN NAME
-------------------------------------------------------------------------
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%TCounty%'
AND ROUTINE_TYPE='PROCEDURE'
-------------------------------------------------------------------------



TO FIND THE MODIFIED TABLES IN A DATABASE
------------------------------------------------
select name,create_date,modify_date from sys.tables
WHERE create_date <> modify_date
ORDER BY modify_Date
------------------------------------------------------------------------


TO FIND INFORMATION ABOUT DATABASE OBJECTS
-----------------------------------------------------------------------
SELECT Specific_Catalog,
Specific_Name,
Routine_Catalog,
Routine_Type,
Routine_Definition,
Created,
Last_Altered
FROM INFORMATION_SCHEMA.ROUTINES
WHERE Created <> Last_Altered
ORDER BY Last_Altered
--------------------------------------------------------


TO GET THE COLUMN NAMES AND DATATYPES IN A TABLE
------------------------------------------------------------------------

USE CRMK
GO
SELECT column_name 'Column Name',
data_type 'Data Type',
character_maximum_length 'Maximum Length'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Country'
--------------------------------------------------------------------------