Dynamically Insert Data To Source Server To Target Server Base On Linked Server

BEGIN
    DECLARE @QUERY VARCHAR(MAX)=''
DECLARE @TARGETTABLENAME VARCHAR(MAX)=''
DECLARE @SOURCETABLENAME VARCHAR(MAX)=''
DECLARE @TARGETSERVER VARCHAR(MAX)='[SERVER\SQLEXPRESS]'
DECLARE @SOURCESERVER VARCHAR(MAX)='[]'

DECLARE @TARGETDB VARCHAR(MAX)=''
DECLARE @SOURCEDB VARCHAR(MAX)=''

SELECT @QUERY ='BEGIN ' + CHAR(10) + CHAR(9) + 'INSERT INTO ' + @TARGETSERVER + '.' + @TARGETDB + '.' + @TARGETTABLENAME + 
CHAR(10) + CHAR(9)+ 'SELECT * FROM ' + @SOURCESERVER + '.' + @SOURCEDB + '.' + @SOURCETABLENAME + CHAR(10) +'END'

PRINT @QUERY
EXECUTE(@QUERY)
END

Identifying Connections Count in SQL Server

SELECT DB_NAME(ST.dbid) AS the_database
        , COUNT(eC.connection_id) AS total_database_connections
FROM sys.dm_exec_connections eC
        CROSS APPLY sys.dm_exec_sql_text (eC.most_recent_sql_handle) ST
        LEFT JOIN sys.dm_exec_sessions eS
                ON eC.most_recent_session_id = eS.session_id
GROUP BY DB_NAME(ST.dbid)
ORDER BY 1;

Remove All New Line & (Feed) From A Variable Or Column In Sql Server

DECLARE @VARIABLE VARCHAR(MAX)=''

SET @VARIABLE= 'Hello Friend 
Thank You So Much For Visit My Blog 
Priyank Gadhiya '

SELECT REPLACE(REPLACE(@VARIABLE, CHAR(13), ''), CHAR(10), '')

Range Between Two Columns Validation Sql Server

DECLARE @FROMVALUE DECIMAL(15,3)=''  
DECLARE @TOVALUE DECIMAL(15,3)=''  
DECLARE @MESSAGE VARCHAR(MAX)=''  

IF  EXISTS(SELECT * FROM dbo.YOURTABLENAME AS C WHERE (C.F_VALUE <= @FROMVALUE AND C.T_VALUE >= @TOVALUE) OR ( (C.F_VALUE BETWEEN @FROMVALUE AND @TOVALUE ) AND (C.T_VALUE BETWEEN @FROMVALUE AND @TOVALUE))
)
BEGIN
SELECT @Message = 'Sorry !! Your Criteria Already Added !!'
RAISERROR (@Message, 16, 1);
END 

Script To All Few Days Modified Stored Procedure In Sql Server

DECLARE @PROCEDURENAME VARCHAR(255)
DECLARE @PROCEDUREID INT
DECLARE @TEXT VARCHAR(MAX)
DECLARE @GRANT_CMD VARCHAR(100)
DECLARE @GRANT_LEVEL VARCHAR(100)
DECLARE @GRANT_ROLE VARCHAR(200)
DECLARE @FILTER_DATE DATETIME = DATEADD(dd, -1, GETDATE())


DECLARE PROC_CURSOR CURSOR FOR

SELECT NAME,OBJECT_ID
FROM SYS.OBJECTS
WHERE TYPE = 'P'AND 
MODIFY_DATE >= @FILTER_DATE
ORDER BY MODIFY_DATE DESC

OPEN PROC_CURSOR
FETCH NEXT FROM PROC_CURSOR INTO @PROCEDURENAME,@PROCEDUREID
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @TEXT=SM.DEFINITION
FROM SYS.SQL_MODULES AS SM
JOIN SYS.OBJECTS AS O ON SM.OBJECT_ID = O.OBJECT_ID
WHERE O.TYPE='P'
AND OBJECT_NAME(SM.OBJECT_ID) = @PROCEDURENAME
PRINT '---------------------------- START PROC ( ' + @PROCEDURENAME + ' ) DATE ' + CONVERT(CHAR(10),@FILTER_DATE,126) +' ---------------------------'
PRINT 'IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N''[DBO].[' + @PROCEDURENAME + ']'') AND TYPE IN (N''P'', N''PC''))'
PRINT 'BEGIN' + CHAR(13) + CHAR(10) + CHAR(9) + 'DROP PROCEDURE [DBO].[' + @PROCEDURENAME + '] '+ CHAR(13) +'END'
PRINT 'GO'
PRINT @TEXT
--SELECT @TEXT
PRINT 'GO'

DECLARE PERMISSIONS_CURSOR CURSOR FOR

SELECT PERMISS.STATE_DESC,PERMISS.PERMISSION_NAME,PRINC.NAME
FROM SYS.DATABASE_PRINCIPALS AS PRINC
INNER JOIN SYS.DATABASE_PERMISSIONS AS PERMISS ON PERMISS.GRANTEE_PRINCIPAL_ID = PRINC.PRINCIPAL_ID
WHERE PERMISS.MAJOR_ID=@PROCEDUREID

OPEN PERMISSIONS_CURSOR
FETCH NEXT FROM PERMISSIONS_CURSOR INTO @GRANT_CMD,@GRANT_LEVEL,@GRANT_ROLE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @GRANT_CMD + ' ' + @GRANT_LEVEL + ' ON [DBO].[' + @PROCEDURENAME + '] TO [' + @GRANT_ROLE + '] AS [DBO]'
PRINT 'GO'
FETCH NEXT FROM PERMISSIONS_CURSOR INTO @GRANT_CMD,@GRANT_LEVEL,@GRANT_ROLE
END
CLOSE Permissions_Cursor
DEALLOCATE Permissions_Cursor

PRINT '---------------------------  END PROC (' + @PROCEDURENAME + ' ) DATE ' + CONVERT(CHAR(10),@FILTER_DATE,126) + '  -------------------------'

FETCH NEXT FROM PROC_CURSOR INTO @PROCEDURENAME,@PROCEDUREID

END
CLOSE PROC_CURSOR
DEALLOCATE PROC_CURSOR

Quickly Idenitfy Most Recently Changed/Modified Stored Procedure In Sql Server

SELECT  NAME ,
        MODIFY_DATE ,
        CREATE_DATE
FROM    SYS.OBJECTS
WHERE   TYPE = 'P'
        AND DATEDIFF(D, MODIFY_DATE, GETDATE()) < 7
ORDER BY MODIFY_DATE DESC;

Simplest Example Of The SQL Server Cursor

DECLARE @FIRST_ID varchar(100);
DECLARE @GET_FIRST_ID CURSOR;

SET @GET_FIRST_ID = CURSOR
FOR
SELECT  COLUMNAME FROM DBO.TABLENAME;

OPEN @GET_FIRST_ID;
FETCH NEXT FROM @GET_FIRST_ID 

INTO @FIRST_ID;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @FIRST_ID;
FETCH NEXT FROM @GET_FIRST_ID INTO @FIRST_ID;
END;

CLOSE @GET_FIRST_ID;
DEALLOCATE @GET_FIRST_ID;

SQL Function To Split String Using Split (Comma Separated String )

CREATE FUNCTION dbo.Fn_Split
    (
      @INPUT VARCHAR(MAX) ,
      @SEPARATOR NVARCHAR(5)
    )
RETURNS @ReturnTabValue TABLE
    (
      Value NVARCHAR(MAX)
    )
AS
    BEGIN
        WHILE ( CHARINDEX(@SEPARATOR, @INPUT) > 0 )
        BEGIN
INSERT INTO @ReturnTabValue( Value)
            SELECT  Value = LTRIM(RTRIM(SUBSTRING(@INPUT, 1,CHARINDEX(@SEPARATOR,@INPUT) - 1)));

            SET @INPUT = SUBSTRING(@INPUT,CHARINDEX(@SEPARATOR, @INPUT)+ LEN(@SEPARATOR), LEN(@INPUT));
        END

        INSERT  INTO @ReturnTabValue( Value )
             SELECT  Value = LTRIM(RTRIM(@INPUT));
        
DELETE  FROM @ReturnTabValue WHERE   ISNULL(Value, '') = ''
        RETURN
    END
GO

Remove Non Alphabetic Characters From String In Sql Server

CREATE FUNCTION [dbo].[RemoveNonAlphabeticChar] 
( @INPUT VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
    BEGIN

        DECLARE @AlphabetsChar AS VARCHAR(100)
        SET @AlphabetsChar = '%[^a-z ]%'
        WHILE PATINDEX(@AlphabetsChar, @INPUT) > 0
            
SET @INPUT = STUFF(@INPUT, PATINDEX(@AlphabetsChar, @INPUT), 1, '')

        RETURN @INPUT
END

Delete All View In SQL Server

DECLARE @VIEWNAME VARCHAR(1000); 

DECLARE curs CURSOR
FOR
    SELECT  [name]
    FROM    sys.objects
    WHERE   TYPE = 'V'; 

OPEN curs; 
FETCH NEXT FROM curs INTO @VIEWNAME; 
WHILE @@fetch_status = 0
    BEGIN 
        EXEC('DROP VIEW ' + @VIEWNAME); 
        PRINT 'DELETED VIEW -> ' + @VIEWNAME;
        FETCH NEXT  FROM curs INTO @VIEWNAME;
    END; 
CLOSE curs; 
DEALLOCATE curs;

Get File Name From File Path In SQL

DECLARE @FILEPATH VARCHAR(1000)

SET @FILEPATH = 'E:\p\New\Folder\FolderSub\My.FileExtension'

SELECT RIGHT(@FILEPATH, Charindex('\', Reverse(@FILEPATH)) - 1) 

Check Given Date Is Weekend Or Not In Sql

DECLARE @DT DATETIME;

SET @DT = Getdate();

SELECT CONVERT(VARCHAR(30), @DT)
       + ' : DateName -  '
       + CONVERT(VARCHAR(10), Datename(dw, @DT))
       + ' : Date In Number  -  '
       + CONVERT(VARCHAR(10), Datepart(dw, @DT)); 

Move One DataBase To New Drive

USE DatabaseName;
GO
ALTER DATABASE DatabaseName MODIFY FILE
(
NAME = DatabaseName_Mdf, FILENAME = 'd:\DatabaseName_Mdf.mdf'
)
GO

ALTER DATABASE tempdb MODIFY FILE
(
NAME = DatabaseName_Log, FILENAME = 'e:\DatabaseName_Log.ldf'
)
GO

Get Logical File Name Of Database

USE DatabaseName
GO
EXEC sp_helpfile
GO

What is the Initial Size of Temp Db ?

SELECT  NAME ,
        SIZE * 8.0 / 1024 'Current Size In MB'
FROM    tempdb.sys.database_files;

SELECT  NAME ,
        SIZE * 8.0 / 1024 'Initial Size in MB'
FROM    master.sys.sysaltfiles
WHERE   dbid = 2;  

Return Output Parameter From Stored Procedure

CREATE PROCEDURE [dbo].[USP_FindName]
      @ID INT,
      @NAME VARCHAR(30) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
     
      SELECT @NAME = NAME FROM dbo.TABLENAME
      WHERE ID = @ID
END

Disable The Windows Authentication Mode Of SQL Server

ALTER LOGIN [ADMIN\SQL2014] DISABLE
GO

Detach Multiple Database In Sql Server

USE MASTER 
DECLARE DATABASES CURSOR
FOR
    SELECT  NAME
    FROM    SYSDATABASES
    WHERE   NAME NOT IN ( 'MASTER', 'TEMPDB', 'MSDB', 'MODEL' ) 
DECLARE @DB SYSNAME 

DECLARE @SQL VARCHAR(5000) 
OPEN DATABASES 

FETCH NEXT FROM DATABASES INTO @DB 
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = 'SP_DETACH_DB ' + @DB 
        EXEC (@SQL) 
        FETCH NEXT FROM DATABASES INTO @DB 

    END 
CLOSE DATABASES 
DEALLOCATE DATABASES  

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

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' 

Find Referencing Objects of a Particular Object (Table)

SELECT SchemaName = Schema_name(obj.schema_id), 
       ObjectName = obj.NAME, 
       ObjectType = obj.type_desc, 
       referenced_schema_name, 
       Referenced_Object_Name = referenced_entity_name, 
       Table_Type_Desc = obj1.type_desc, 
       referenced_server_name, 
       referenced_database_name 
FROM   sys.sql_expression_dependencies sed 
       LEFT JOIN sys.objects obj ON sed.referencing_id = obj.[object_id] 
       LEFT JOIN sys.objects obj1 ON sed.referenced_id = obj1.[object_id] 
WHERE  1 = 1 
       AND referenced_entity_name IN( 'TAB1', 'TAB2' ) 
ORDER  BY referenced_entity_name ASC 

List Primary Keys For All Tables From Source Data Base

DECLARE @SourceDB sysname = 'DataBaseName'
IF DB_ID(@SourceDB) IS NULL
BEGIN
  PRINT 'Error: Unable to find the database ' + @SourceDB + '!!!'
  RETURN
END
EXEC ('WITH CTE AS 
( 
           SELECT      IC.INDEX_ID + IC.OBJECT_ID AS INDEXID,T.NAME AS TABLENAME 
                       ,I.NAME AS INDEXNAME
,CASE WHEN IC.IS_INCLUDED_COLUMN =0 THEN
C.NAME END AS COLUMNNAME
,CASE WHEN IC.IS_INCLUDED_COLUMN =1 THEN
C.NAME END AS INCLUDEDCOLUMN
,I.TYPE_DESC 
                       ,I.IS_PRIMARY_KEY,I.IS_UNIQUE 
           FROM  ' + @SOURCEDB + '.SYS.INDEXES I 
           INNER JOIN ' + @SOURCEDB + '.SYS.INDEX_COLUMNS IC 
                   ON  I.INDEX_ID    =   IC.INDEX_ID 
                   AND I.OBJECT_ID   =   IC.OBJECT_ID 
           INNER JOIN ' + @SOURCEDB + '.SYS.COLUMNS C 
                   ON  IC.COLUMN_ID  =   C.COLUMN_ID 
                   AND I.OBJECT_ID   =   C.OBJECT_ID 
           INNER JOIN ' + @SOURCEDB + '.SYS.TABLES T 
                   ON  I.OBJECT_ID = T.OBJECT_ID 
) 
SELECT ''' + @SOURCEDB + ''' AS SOURCEDATABASENAME,C.TABLENAME TABLE_NAME,C.INDEXNAME INDEX_NAME,C.TYPE_DESC INDEX_TYPE ,C.IS_PRIMARY_KEY IS_PRIMARY_KEY,C.IS_UNIQUE IS_UNIQUE
       ,STUFF( ( SELECT '',''+ A.COLUMNNAME FROM CTE A WHERE C.INDEXID = A.INDEXID FOR XML PATH('''')),1 ,1, '''') AS COLUMNS
       ,STUFF( ( SELECT '',''+ A.INCLUDEDCOLUMN FROM CTE A WHERE C.INDEXID = A.INDEXID FOR XML PATH('''')),1 ,1, '''') AS INCLUDED_COLUMNS
FROM   CTE C 
GROUP  BY C.INDEXID,C.TABLENAME,C.INDEXNAME,C.TYPE_DESC,C.IS_PRIMARY_KEY,C.IS_UNIQUE 
ORDER  BY C.TABLENAME ASC,C.IS_PRIMARY_KEY DESC; ');

Two Data Base Difference Table List

SELECT T1.TABLE_NAME, 'DataBase First' AS DBNAME FROM DataBase First.[INFORMATION_SCHEMA].[TABLES] T1 WHERE TABLE_NAME NOT IN ( SELECT T2.TABLE_NAME FROM DataBase Second.[INFORMATION_SCHEMA].[TABLES] T2 )

Two Data Base Difference Column Data Type

SELECT 'DBFirst' AS DbName,
  C1.TABLE_NAME, 
       C1.COLUMN_NAME, 
       C1.DATA_TYPE, 
  '      <=>' AS DIFFERENCE,
  'DBSecond' AS DbName,
       C2.TABLE_NAME, 
       C2.DATA_TYPE, 
       C2.COLUMN_NAME 
FROM   DBFirst.[INFORMATION_SCHEMA].[COLUMNS] C1 
       INNER JOIN DBSecond.[INFORMATION_SCHEMA].[COLUMNS] C2 
               ON C1.COLUMN_NAME = C2.COLUMN_NAME 
WHERE  1 = 1 
       AND C1.TABLE_NAME = C2.TABLE_NAME 
       AND C1.DATA_TYPE <> C2.DATA_TYPE 

List Primary Keys For All Tables (Comma Separated)

SELECT OUTTAB.TABLE_NAME, 

       STUFF ((SELECT ', ' + KCU.COLUMN_NAME 

               FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS INRTAB 

                      JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 

                        ON KCU.CONSTRAINT_SCHEMA = INRTAB.CONSTRAINT_SCHEMA 

                           AND KCU.CONSTRAINT_NAME = INRTAB.CONSTRAINT_NAME 

                           AND KCU.TABLE_SCHEMA = INRTAB.TABLE_SCHEMA 

                           AND KCU.TABLE_NAME = INRTAB.TABLE_NAME 

               WHERE  INRTAB.TABLE_NAME = OUTTAB.TABLE_NAME 

               --   AND  INRTAB.COLUMN_NAME = OUTTAB.COLUMN_NAME 

               ORDER  BY INRTAB.TABLE_NAME 

               FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, SPACE(0 

       )) 

FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS OUTTAB 

WHERE  OUTTAB.TABLE_NAME = '' 

GROUP  BY OUTTAB.TABLE_NAME 

Drop All The Tables From SQL Server Database Without Using Enterprise Manager

DECLARE @SQL NVARCHAR(MAX)=''

SELECT @SQL += 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + CHAR(13)
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'
  ORDER BY TABLE_NAME
SELECT @SQL

OR

SELECT 'DROP TABLE [' + SCHEMA_NAME(SCHEMA_ID) + '].[' + NAME + ']' FROM SYS.TABLES ORDER BY NAME

Find All The User-Defined Functions In a Database

SELECT  NAME AS FUNCTION_NAME ,
        SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME ,
        TYPE_DESC ,
        CREATE_DATE ,
        MODIFY_DATE
FROM    SYS.OBJECTS
WHERE   TYPE_DESC LIKE '%FUNCTION%'
ORDER BY MODIFY_DATE DESC

Data Base OwnerShip With User Permission

-- Data Base OwnerShip With User Permission
USE [master]

GO
IF  EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'YourUser')
BEGIN
 DROP LOGIN [YourUser]
END 

IF EXISTS (SELECT * FROM sys.syslogins WHERE name = N'YourUser') 
BEGIN
 DROP LOGIN [YourUser] 
END

CREATE LOGIN [YourUser] WITH PASSWORD=N'YourUser' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [DataBase]
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'YourUser')
BEGIN
 DROP USER [YourUser]
END 

GO
CREATE USER [YourUser] FOR LOGIN [YourUser]
GO
USE [DataBase]
GO
ALTER USER [YourUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [DataBase]
GO
ALTER ROLE [db_owner] ADD MEMBER [YourUser]
GO

Dynamically Multiple Table Trigger Created In Sql Server

CREATE PROC TABLE_TRIGGER
@TABLENAME VARCHAR(MAX)=''
AS 
BEGIN
DECLARE @DATABASECONNECTION VARCHAR(MAX)=''
BEGIN

BEGIN TRY
BEGIN TRAN

DECLARE @COLUMLIST VARCHAR(MAX)
DECLARE @PRIMARYKEY VARCHAR(MAX)
DECLARE @DESTINATIONCOL VARCHAR(MAX)

SELECT @COLUMLIST = COALESCE(@COLUMLIST,'','') + name +',' FROM sys.columns WHERE OBJECT_NAME(object_id) = @TABLENAME
SELECT @PRIMARYKEY = COALESCE(@PRIMARYKEY,'','') +'Sou.' + COL_NAME(object_id,index_id) 
 +'= Dest.' + COL_NAME(object_id,index_id)
FROM sys.indexes WHERE OBJECT_NAME(object_id) = @TABLENAME

SET @COLUMLIST = SUBSTRING(@COLUMLIST,0,LEN(@COLUMLIST))
SET @DESTINATIONCOL = CHAR(13)
SELECT @DESTINATIONCOL = COALESCE(@DESTINATIONCOL,'','') + CHAR(9)+ CHAR(9)+ CHAR(9) + 'Dest.' + Col.NAME + ' = Sou.' + Col.NAME + ', '+ CHAR(13)
FROM   SYS.OBJECTS Obj 
LEFT JOIN SYS.COLUMNS Col 
ON Col.OBJECT_ID = Obj.OBJECT_ID 
 WHERE  Obj.NAME = @TABLENAME 
 ORDER  BY COLUMN_ID 

SET @DESTINATIONCOL = SUBSTRING(@DESTINATIONCOL,0,LEN(@DESTINATIONCOL)-2)

DECLARE @sql NVARCHAR(MAX) = 'CREATE TRIGGER TR_'+ @TABLENAME + '_MYTRIGGER ON '+@TABLENAME+'
AFTER INSERT,UPDATE,DELETE AS 
BEGIN 
MERGE INTO ' + @DATABASECONNECTION + @TABLENAME +' As Dest
USING 
( 
SELECT '+ @COLUMLIST+ ' FROM INSERTED 
) AS Sou ON '+ @PRIMARYKEY + ' 
WHEN MATCHED THEN
UPDATE SET ' 
+CHAR(9) + @DESTINATIONCOL +  CHAR(13)+
CHAR(9)+ ' WHEN NOT MATCHED THEN
INSERT('+ @COLUMLIST +')
VALUES ('+ @COLUMLIST +');
END'

SELECT @sql
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR('Trigger Not Created !!', 18, 1)
ROLLBACK TRAN
END CATCH
END
END 

Destination Column To Source Column In Sql Server

SELECT Obj.NAME AS TableName,Col.NAME AS ColumnName, 
       'Dest.' + Col.NAME + ' = Sou.' + Col.NAME + ' ,' AS DestinationCol 
FROM   SYS.OBJECTS Obj 
       LEFT JOIN SYS.COLUMNS Col 
              ON Col.OBJECT_ID = Obj.OBJECT_ID 
WHERE  Obj.NAME = 'TABLENAME' 
ORDER  BY COLUMN_ID 

Comma Separated Columns List With Table In Sql Server

DECLARE @LIST VARCHAR(8000) 

SELECT @LIST = COALESCE(@LIST + ', ', '') 
               + Cast(COL.NAME AS VARCHAR) 
FROM   SYS.OBJECTS OBJ 
       LEFT JOIN SYS.COLUMNS COL 
              ON COL.OBJECT_ID = OBJ.OBJECT_ID 
WHERE  OBJ.NAME = 'TABLENAME' 
ORDER  BY COLUMN_ID 

DataBase Backup In Single Query

BACKUP DATABASE DATABASENAME NAME TO DISK='D:/DATABASE.BAK'

DataBase Backup Query

DECLARE @PATHNAME     NVARCHAR(512), 
        @DATABASENAME NVARCHAR(512) 

SET @DATABASENAME = 'DATABASENAME' 
SET @PATHNAME = 'D:\' + @DATABASENAME + '-' 
                + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' 
                + REPLACE((CONVERT(VARCHAR(8), GETDATE(), 108)), ':', '-') 
                + '.BAK' 

BACKUP DATABASE @DATABASENAME TO DISK = @PATHNAME WITH NOFORMAT, NOINIT, NAME = 
N'', SKIP, NOREWIND, NOUNLOAD, STATS = 10 

GO 

Trigger to Automatically Manage the Modified Date Field

CREATE TRIGGER TRIGGEREMPUPDATE 
ON DBO.EMPLOYEE 
AFTER UPDATE 
AS 
  BEGIN 
      UPDATE [DBO].EMPLOYEE 
      SET    MODIFIEDDATE = GETDATE() 
      WHERE  EMPLOYEE.EMPID IN (SELECT DISTINCT INSERTED.EMPID 
                                FROM   INSERTED) 
  END 

Column Exists In SQL Server Table

IF EXISTS
(
          SELECT 1 
          FROM   SYS.COLUMNS 
          WHERE  NAME = N'EMPID' 
                       AND OBJECT_ID = OBJECT_ID(N'EMPLOYEEMAST')
) 
BEGIN 
          PRINT 'COLUMN EXISTS !!' 
END 

Using Common Table Expression (CTE) In Sql Server

WITH EMPCTE (EMP_CODE,  EMP_NAME,  REMARK) 
AS ( SELECT EMP_CODE, 
                     EMP_NAME, 
                     REMARK, 
                     BIRTHDATE, 
                     JOINDATE 
         FROM   EMPMAST
      ) 

SELECT EMP_CODE, 
            EMP_NAME, 
            REMARK 
 FROM   EMPCTE  AS CTE 

Find The Number Of Users Connected To A SQL Server DATABASE

SELECT  D_E_S.DATABASE_ID,
DB_NAME(D_E_S.DATABASE_ID) AS DataBaseName,
COUNT(D_E_S.SESSION_ID) AS Total_Connection,
D_E_S.ORIGINAL_LOGIN_NAME AS Org_Login_Name,
D_E_S.HOST_NAME AS ComputerName
FROM SYS.DM_EXEC_SESSIONS D_E_S
WHERE D_E_S.DATABASE_ID = 5
GROUP BY D_E_S.DATABASE_ID,D_E_S.ORIGINAL_LOGIN_NAME,D_E_S.HOST_NAME
ORDER BY 1,2,3

Table Variables With Using Primary Keys and Indexes

Declaring a Table Variable with a Primary Key

DECLARE @SQLFORMMAST TABLE
(       
           FORMID INT NOT NULL,        
           FORMNAME VARCHAR(100),        
           DESCRIPTION NVARCHAR(100),    
           PRIMARY KEY (FORMID)
)

Declaring a Table Variable with a Composite Primary Key

DECLARE @SQLFORMMAST TABLE

(       

           FORMID INT NOT NULL,        

           FROMNAME VARCHAR(100),        

           DESCRIPTION NVARCHAR(100)               

  PRIMARY KEY (FORMID)

)

Declaring a Table Variable with a Composite Unique Index

DECLARE @SQLFORMMAST TABLE

(       

           FORMID INT PRIMARY KEY,        

           FORMNAME VARCHAR(100),        

           DESCRIPTION NVARCHAR(100)               

  UNIQUE CLUSTERED (FORMID) 

)