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

Paging (Skip - Take) Functionality With this Query

WITH Results AS 
(
SELECT ID, 
  VALUE,
  ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM   DBO.TABLENAME
) 
SELECT Results.ID, 
       Results.VALUE 
FROM   Results 
WHERE  RowNumber BETWEEN 10 AND 15 

Removing Last Character With Dynamic Query

DECLARE @VariableList varchar(MAX)=''
SELECT @VariableList += 'SELECT * FROM '+ NAME + CHAR(13) + 'UNION ALL ' + CHAR(13)
FROM sys.tables
SELECT left(@VariableList,LEN(@VariableList) - 11) + CHAR(13) AS VariableList 
PRINT(@VariableList)

SQL Server's Last Start Date and Time

SELECT @@Servername                                    AS ServerName, 
       create_date                                              AS ServerStarted, 
       Datediff(s, create_date, Getdate()) / 86400.0 AS DaysRunning, 
       Datediff(s, create_date, Getdate())               AS SecondsRunnig 
FROM   sys.databases 
WHERE  NAME = 'DBNAME'