Multiple Table Insert Query To Another Target Server Database Table In Sql Server

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

DECLARE @TARGETDB VARCHAR(MAX)='DBNAME'
DECLARE @SOURCEDB VARCHAR(MAX)='DBNAME1'
Declare @Var nvarchar(MAX)
DECLARE @RES VARCHAR(MAX)=''

Set @Var =@TARGETTABLENAME
DECLARE @XML AS XML
DECLARE @Delimiter AS CHAR(1) =','
SET @XML = CAST(('<X>'+REPLACE(@Var,@Delimiter ,'</X><X>')+'</X>') AS XML)
DECLARE @temp TABLE (TABNAME VARCHAR(max))
INSERT INTO @temp
SELECT N.value('.', 'VARCHAR(max)') AS ID FROM @XML.nodes('X') AS T(N)

DECLARE cur CURSOR FOR
SELECT * FROM @temp
OPEN cur

FETCH NEXT FROM cur INTO @RES

WHILE @@FETCH_STATUS = 0 
BEGIN
SELECT @QUERY ='BEGIN ' +CHAR(10) +CHAR(9) +'--EXECUTE TABLENAME ' +@RES + CHAR(10) + CHAR(9) + 'SET IDENTITY_INSERT '+ @TARGETSERVER + '.' + @TARGETDB + '.' + @RES +  ' ON '  + 
CHAR(10) + CHAR(9) + 'INSERT INTO ' + @TARGETSERVER + '.' + @TARGETDB + '.' + @RES + 
CHAR(10) + CHAR(9) + 'SELECT * FROM ' + @SOURCESERVER + '.' + @SOURCEDB + '.' + @RES + CHAR(10) + CHAR(9)+
'SET IDENTITY_INSERT '+ @TARGETSERVER + '.' + @TARGETDB + '.' + @RES +  ' OFF '+ CHAR(10) + 'END'

PRINT @QUERY
--EXECUTE(@QUERY)
FETCH NEXT FROM cur INTO @RES
END




END

Comma Separted Value To Temp Table In Sql Server

DECLARE @Var nvarchar(MAX)

Set @Var ='TABLENAME,TAB'
DECLARE @XML AS XML
DECLARE @Delimiter AS CHAR(1) =','
SET @XML = CAST(('<X>'+REPLACE(@Var,@Delimiter ,'</X><X>')+'</X>') AS XML)
DECLARE @temp TABLE (TABNAME VARCHAR(max))
INSERT INTO @temp
SELECT N.value('.', 'VARCHAR(max)') AS ID FROM @XML.nodes('X') AS T(N)





SELECT * FROM @temp

Sql server drop all foreign key constraints on a table

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
exec (@sql)




end

Comma Separated Column Value In SQL Server

DECLARE @DATE VARCHAR(MAX) = '';

SELECT 
STUFF(
(
SELECT ', ' + CAST(COLUMNNAME AS VARCHAR(MAX))
FROM dbo.TABLENAME AS T WITH (NOLOCK)
WHERE 1 =1 
AND T.DATE = @DATE
FOR XML PATH('')
) , 1, 2, ''
);

All Connected User List Out Sql Server (Login User)

SELECT hostname 'Computer Name', DB_NAME(dbid) 'Database', program_name AS Program_Name, COUNT(program_name) 'TotalConnection'
FROM
       master.dbo.sysprocesses
WHERE
       hostname <> ''
       AND DB_NAME(dbid) <> 'master'
GROUP BY
       hostname, DB_NAME(dbid), Program_Name;

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;