SQL Server: Get RowCount Of All Tables Without the Overhead

Avoid overhead with this completely awesome SQL Server query that returns the row-count of all tables without actually having to query each table.  Instead, it just pulls from the statistics:

SELECT ‘[‘ + SCHEMA_NAME(t.schema_id)+ ‘].[‘ + t.name + ‘]’ AS full_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id =i.id AND i.indid < 2

SQL Server: Get First and Last Days Of Month

DECLARE @min DATETIME
DECLARE @max DATETIME
DECLARE @now DATETIME
SET @now = GETDATE();

–get first day of last month:

SET @min = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(DATEADD(mm,1,@now))-1),DATEADD(mm,-1,@now)),101) 

–get the last day of last month

SET @max = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(@now)),@now),101) 

–get the first day of current month
SET @min = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(@now)-1),@now),101)  

–get the last day of current month

SET @max = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(DATEADD(mm,1,@now))),DATEADD(mm,1,@now)),101) 

SQL Server : Check If Index Exists On Schema

–create function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[IndexExistsOnSchema]
(
      @SCHEMA VARCHAR(50),
      @INDEX  VARCHAR(255)
)
RETURNS INT
AS
BEGIN
      DECLARE @CNT INT
      SET @CNT = 0
      SELECT @CNT = COUNT(*)
      FROM SYS.INDEXES
      WHERE OBJECT_ID = OBJECT_ID(@SCHEMA)
      AND name = @INDEX
      RETURN @CNT
END 
GO
–use function:


IF (SELECT DBO.INDEXEXISTSONSCHEMA(@SCHEMA_NAME, @INDEX_NAME)) > 0
EXEC(‘ALTER INDEX [INDEX_NAME] ON ‘ + @SCHEMA_NAME + ‘ DISABLE’);
–or
IF (SELECT DBO.INDEXEXISTSONSCHEMA(@SCHEMA_NAME,@INDEX_NAME)) > 0
EXEC(‘ALTER INDEX [INDEX_NAME] ON ‘ + @SCHEMA_NAME+ ‘ REBUILD’);

SQL Server: Generic Try/Catch Snippet

BEGIN TRY

— do stuff

END TRY

BEGIN CATCH

                  DECLARE @PROC AS VARCHAR(100)
                  DECLARE @SERVER AS VARCHAR(200)
                  DECLARE @ERROR AS VARCHAR(4000)

                  SELECT @PROC = ERROR_PROCEDURE(),
                      @SERVER = @@SERVERNAME,
                      @ERROR = ERROR_MESSAGE();

END CATCH;

SQL Server: Common Table Expression

Scenario:  Need to write a query based on two tables.  The tables have a one-to-many relationship.  The “bug_comments” table can have many records for one table in the “bug” table.  However, I do not want all of the records in the “bug_comments” table, I only want the most recent record.  Also, I don’t want to write a subquery or use a cursor.

WITH cte (bc_bug, bc_id)
AS
(SELECT bc_bug, MAX(bc_id)
FROM bug_comments
WHERE LOWER(bc_type) = ‘comment’
GROUP BY bc_bug )
SELECT DISTINCT 
b.bg_id AS [ID],
b.bg_short_desc AS [DESCR],
bc.bc_comment AS [COMMENT]
FROM bugs AS b LEFT OUTER JOIN
bug_comments AS bc ON b.bg_id = bc.bc_bug LEFT OUTER JOIN
cte ON cte.bc_bug = b.bg_id AND cte.bc_id = bc.bc_id
WHERE b.bg_status = 1 and
((cte.bc_bug = bugs.bg_id and cte.bc_id = bc.bc_id)
ORDER BY b.bg_id DESC