Data and Analytics

Please use below code to find when a stored procedure was last created or modified. SELECT name , create_date , modify_date FROM sys . objects WHERE type = 'P' AND name = '[Name of the stored procedure]' GO Please Note: This code is tested on SQL 2005/2008/2008 R2

Sometime we need to calculate what is the total size of all backups of all databases hosted on a SQL Server instance. In that case, please use below code. Note: This code will calculate total size of full backups of all databases hosted on the SQL Server instance in GB and MB . However you may change the combination of device_type and type value to suit your specific query. use msdb go /* Please change device_type and backupset type combination as you need. backupmediafamily device_type 2 = File 5 = Tape backupset type L = Log I = Differential D = Full */ SELECT       ( SUM ( backup_size ) + SUM ( 1536 )) / 1024 / 1024 As MBs ,       ( SUM ( backup_size ) + SUM ( 1536 )) / 1024 / 1024 / 1024 As GBs FROM       backupset INNER JOIN (       SELECT             database_name ,             MAX ( backup_start_date ) as LastFullBackupDate       FROM             backupset       WHERE             media_set_id IN

You may experience below error while executing dbcc checkdb (actually any dbcc check command) on a database: Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation. Msg 7926, Level 16, State 1, Line 1 Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details. This error is created when at least one of the below condition is true: At least one other connection is using the database against which you run the DBCC CHECK command. The database contains at least one file group that is marked as read-only. Please follow below steps to resolve this problem: 1.   Create a database snapshot of the database for which you want to perform the checks. 2.   Run the DBCC CHECK command against the database s

If you need to take row count (number of rows) of all tables in a database, please use following code: USE <DATABASE NAME> GO SELECT      SCHEMA_NAME(A.schema_id) + '.' +         A.Name, SUM(B.rows) AS 'RowCount' FROM        sys.objects A INNER JOIN sys.partitions B ON A.object_id = B.object_id WHERE       A.type = 'U' GROUP BY    A.schema_id, A.Name GO