Find Row Count Of Tables Based On Cursor

DECLARE @TName VARCHAR(MAX); 
DECLARE @Cnt VARCHAR(MAX); 
DECLARE TABLENAME CURSOR
FOR
    SELECT  TNAME
    FROM    TABLENAME
    WHERE   TYPE = 'L'; 

OPEN TABLENAME; 

FETCH NEXT FROM TABLENAME INTO @TName; 

WHILE @@FETCH_STATUS = 0
    BEGIN 
        SET NOCOUNT ON; 

        SELECT  @Cnt = COUNT(*)
        FROM    INFORMATION_SCHEMA.COLUMNS
        WHERE   TABLE_NAME = @TName; 

        PRINT 'Table : ' + @TName + ' (' + @Cnt + ')'; 

        FETCH NEXT FROM TABLENAME INTO @TName; 
    END;

CLOSE TABLENAME; 
DEALLOCATE TABLENAME; 

Working With The TRY…CATCH Block in Sql Server

DECLARE @COLUMNID INT ,
              @COLUMNNAME INT;

BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE  TABLENAME
        SET     COLUMNNAME = @COLUMNNAME
        WHERE   COLUMNID = @COLUMNID;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        DECLARE @ErrorNumber INT = ERROR_NUMBER();
        DECLARE @ErrorLine INT = ERROR_LINE();
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        PRINT 'Actual Error Number: ' + CAST(@ErrorNumber AS VARCHAR(10));
        PRINT 'Actual Line Number: ' + CAST(@ErrorLine AS VARCHAR(10));

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;
GO

Return Custom Error Message

DECLARE @CustomErrorString VARCHAR(100),
        @Value             INT = 5;

IF @Value >= 5
  BEGIN
      SET @CustomErrorString = 'Value Is Greater Than or Equal To '
                               + CONVERT(VARCHAR(10), @Value);

      RAISERROR (@CustomErrorString,10,1,50,50);

      RETURN; --Exit Now
  END; 

Drop All The Stored Procedure From SQL Server Database Without Using Enterprise Manager

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']'
FROM SYS.PROCEDURES
ORDER BY NAME

Rebuild All Indexes For All Tables And All Databases

DECLARE @DATABASE VARCHAR(255)   

DECLARE @TABLE VARCHAR(255)  

DECLARE @CMD NVARCHAR(500)  

DECLARE @FILLFACTOR INT 

SET @FILLFACTOR = 90 

DECLARE DATABASECURSOR CURSOR FOR  

SELECT NAME FROM MASTER.DBO.SYSDATABASES   

WHERE NAME NOT IN ('MASTER','MODEL','MSDB','TEMPDB')   

ORDER BY 1  

OPEN DATABASECURSOR  

FETCH NEXT FROM DATABASECURSOR INTO @DATABASE  

WHILE @@FETCH_STATUS = 0  

BEGIN  

SET @CMD = 'DECLARE TABLECURSOR CURSOR FOR SELECT ''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + 

TABLE_NAME + '']'' AS TABLENAME FROM [' + @DATABASE + '].INFORMATION_SCHEMA.TABLES 

WHERE TABLE_TYPE = ''BASE TABLE'''   

   EXEC (@CMD)  

   OPEN TABLECURSOR   

   FETCH NEXT FROM TABLECURSOR INTO @TABLE   

   WHILE @@FETCH_STATUS = 0   

   BEGIN   

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)

       BEGIN

           -- SQL 2005 OR HIGHER COMMAND 

           SET @CMD = 'ALTER INDEX ALL ON ' + @TABLE + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@FILLFACTOR) + ')' 

  --EXEC (@CMD) 

           PRINT (@CMD) 

       END

       ELSE

       BEGIN

          -- SQL 2000 COMMAND 

          DBCC DBREINDEX(@TABLE,' ',@FILLFACTOR)  

       END

       FETCH NEXT FROM TABLECURSOR INTO @TABLE   

   END   

   CLOSE TABLECURSOR   

   DEALLOCATE TABLECURSOR  

   FETCH NEXT FROM DATABASECURSOR INTO @DATABASE  

END  

CLOSE DATABASECURSOR   

DEALLOCATE DATABASECURSOR

Rebuild All Indexes For All Tables And One Databases

DECLARE @TABLENAME VARCHAR (255)

DECLARE @SQL NVARCHAR (500)

DECLARE @FILLFACTOR INT

SET @FILLFACTOR = 80

DECLARE TABLECURSOR CURSOR

FOR

SELECT OBJECT_SCHEMA_NAME([OBJECT_ID]) + '.' + NAME AS TABLENAME

FROM SYS.TABLES

ORDER BY NAME

OPEN TABLECURSOR;

FETCH NEXT FROM TABLECURSOR

INTO @TABLENAME;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'ALTER INDEX ALL ON ' + @TABLENAME + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR (3), @FILLFACTOR) + ')'

PRINT( @SQL )

--EXEC( @SQL )

FETCH NEXT FROM TABLECURSOR

INTO @TABLENAME

END

CLOSE TABLECURSOR

DEALLOCATE TABLECURSOR

GO

Validation Before Create New Table Sql Server

CREATE TABLE [dbo].[ColumnName](
[Id] [int] NOT NULL,
[ColumnName] [varchar](50) NULL
) ON [PRIMARY]


GO

ALTER TRIGGER TABLECOLUMN_CHECK ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
 
        DECLARE @Text VARCHAR(MAX)
        SELECT  @Text = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                                          'nvarchar(max)')  
        DECLARE @NewList VARCHAR(MAX)
        SET @NewList = REPLACE(SUBSTRING(@Text, CHARINDEX('(', @Text, 0) + 1,
                                         LEN(@Text)), ')', '') -- CHARINDEX('(',@Text,0)
        DECLARE @RequireColumn INT
        SELECT  @RequireColumn = COUNT(1)
        FROM    dbo.ColumnName

        IF ( @RequireColumn <> ( SELECT COUNT(1) FROM   dbo.ColumnName
                                 INNER JOIN 
( SELECT SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Value)),
                                                              CHAR(9), ''),
                                                              CHAR(13), ''),
                                                              CHAR(10), ''), 0,
                                                              CHARINDEX(CHAR(32),
                                                              REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Value)),
                                                              CHAR(9), ''),
                                                              CHAR(13), ''),
                                                              CHAR(10), ''))) ColumnName
                                                FROM   dbo.Split(@NewList,',')
                                 ) AS ColumnList ON ColumnList.ColumnName = dbo.ColumnName.ColumnName
                               )
)
        BEGIN
DECLARE @PrintMsg VARCHAR(MAX)
            SELECT  @PrintMsg = STUFF((SELECT   ', ' + CAST(ColumnName AS VARCHAR(50)) [text()] 
  FROM  dbo.ColumnName
                                           WHERE ColumnName NOT IN 
  (
SELECT  SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Value)),
CHAR(9), ''),
CHAR(13), ''),
CHAR(10), ''), 0,
CHARINDEX(CHAR(32),
REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Value)),
CHAR(9), ''),
CHAR(13), ''),
CHAR(10), '')))
                                            FROM    dbo.Split(@NewList,',') 
) FOR XML PATH('') ,TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')
DECLARE @DisplayMsg VARCHAR(max)
SET @DisplayMsg = 'Sorry !! Table Not Created Because Required Column ( '+ @PrintMsg + ' ) Does Not Exits In This Table '
                RAISERROR(@DisplayMsg,16,1)                
                ROLLBACK 
        END 
END