Sql Utility Stored Procedures

GET DATABASE SIZE
------------------------------------------------------------------

SELECT

database_name = DB_NAME(database_id)

, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))

, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))

, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))

FROM sys.master_files WITH(NOWAIT)

WHERE DB_NAME(database_id) = 'master' –-- your database name

GROUP BY database_id-------------------------------------------------------------------------

GET TABLE SIZE
------------------------------------------------------------------
SP_SPACEUSED 'YOUR TABLE NAME'

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


GET LIST OF TABLES AND THEIR ROW COUNTS
------------------------------------------------------------------
 SELECT SO.name AS [TableName], MAX(SI.Rows) AS [RowsCount]
 FROM sysobjects SO , sysindexes SI
 WHERE SO.type='u' AND SI.id=  object_id(SO.name) 
 GROUP BY SO.name
 ORDER BY RowsCount DESC

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



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 SPs/VIEWS/TABLES/FUNCTIONS COUNT IN A DATABASE
----------------------------
 select count(1) [Tables] from sys.objects where type='u'
select count(1)[Procedures] from sys.objects where type='p'
select count(1)[Views] from sys.objects where type='v'
select count(1)[ScalarFunctions] from sys.objects where type='FN'
select count(1)[TableFunctions] from sys.objects where type='TF'

SELECT * FROM [ATS].[sys].[triggers]

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


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%'


If we want to get a list of all procedures which contains particular table name we need to write the query like as shown below

SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%YourTableName%'


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


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'
--------------------------------------------------------------------------


TO FIND ALL THE TRIGGERS IN A DATABASE
------------------------------------------------------------------------

SELECT S2.[name] TableName, S1.[name] TriggerName,
CASE
WHEN S2.deltrig = s1.id  THEN 'Delete'
WHEN S2.instrig = s1.id THEN 'Insert'
WHEN S2.updtrig = s1.id THEN 'Update'
END 'TriggerType' , 'S1',s1.*,'S2',s2.*
FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'

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

Posted in: SQL,SQL UTILITY FUNCTIONS,SQL_UTILITY_SPs