Drawing the world RSS FEED

USE master;
GO

DECLARE @LoginName nvarchar(218);
SET @LoginName = '[로그인명]';



DECLARE @Query nvarchar(4000);
DECLARE @SPID int;

DECLARE Kill_Cursor CURSOR FOR
	SELECT SPID
	FROM  master.dbo.sysprocesses
	WHERE SPID <> @@SPID
	  AND loginame = @LoginName;

OPEN Kill_Cursor;

FETCH Kill_Cursor INTO @SPID;
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @Query = 'KILL ' + CONVERT(varchar, @SPID) + ';';
	PRINT @Query;
	EXECUTE sp_executesql @Query;
	FETCH Kill_Cursor INTO @SPID;
END;

CLOSE Kill_Cursor;
DEALLOCATE Kill_Cursor;
GO

--Edit Here
USE [YourDB];
GO

SET NOCOUNT ON;


IF OBJECT_ID('tempdb.dbo.#AlterCollateQuery', 'U') IS NOT NULL
	DROP TABLE #AlterCollateQuery;
GO

CREATE TABLE #AlterCollateQuery
(
	 ExecuteOrder int identity(1,1)
	,Query nvarchar(4000)
)
GO

IF OBJECT_ID('dbo.F_GetForeignKeyColumns', 'FN') IS NOT NULL
	DROP FUNCTION dbo.F_GetForeignKeyColumns;
GO

CREATE FUNCTION dbo.F_GetForeignKeyColumns
(
	 @object_id	int
)
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @return				varchar(4000);
	DECLARE @referenced_object_id	int;
	DECLARE @parent_columns		varchar(4000);
	DECLARE @referenced_columns	varchar(4000);
	
	SET @parent_columns = '';
	SET @referenced_columns = '';

	SELECT
		 @referenced_object_id = a.referenced_object_id
		,@parent_columns = @parent_columns + N', [' + b.name + N']'
		,@referenced_columns = @referenced_columns + N', [' + c.name + N']'
	FROM sys.foreign_key_columns AS a
		INNER JOIN sys.columns AS b ON b.object_id = a.parent_object_id AND b.column_id = a.parent_column_id
		INNER JOIN sys.columns AS c ON c.object_id = a.referenced_object_id AND c.column_id = a.referenced_column_id
	WHERE a.constraint_object_id = @object_id;
	
	SELECT @return = N'FOREIGN KEY(' + STUFF(@parent_columns, 1, 2, N'') + N') REFERENCES [' + a.name + N'].[' + b.name + N'](' + STUFF(@referenced_columns, 1, 2, N'') + N')'
	FROM sys.schemas AS a
		INNER JOIN sys.tables  AS b ON a.schema_id = b.schema_id
	WHERE b.object_id = @referenced_object_id;
	
	RETURN @return;
END;
GO



IF OBJECT_ID('dbo.F_GetIndexColumns', 'FN') IS NOT NULL
	DROP FUNCTION dbo.F_GetIndexColumns;
GO

CREATE FUNCTION dbo.F_GetIndexColumns
(
	 @object_id	int
	,@index_id	int
)
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @Return nvarchar(4000);
	SET @Return = '';
	
	SELECT @Return = @Return + N', [' + b.name + N'] ' + CASE WHEN a.is_descending_key = 0 THEN N'ASC' ELSE N'DESC' END
	FROM sys.index_columns AS a
		INNER JOIN sys.columns AS b ON a.object_id = b.object_id AND a.column_id = b.column_id
	WHERE a.object_id = @object_id
	  AND a.index_id = @index_id
	ORDER BY a.index_column_id;
	
	RETURN STUFF(@Return, 1, 2, '');
END;
GO

IF OBJECT_ID('tempdb.dbo.#QueryList', 'U') IS NOT NULL
	DROP TABLE #QueryList;
GO

CREATE TABLE #QueryList
(
	 executeDropOrder int
	,dropQuery nvarchar(4000)
	,executeCreateOrder int
	,createQuery nvarchar(4000)
);
GO




DECLARE @collation_name varchar(4000);
--Edit Here
SET @collation_name = N'Korean_Wansung_CI_AS';




INSERT INTO #QueryList(executeDropOrder, dropQuery, executeCreateOrder, createQuery)
SELECT
	 1 AS executeDropOrder
	,N'ALTER TABLE [' + d.name + N'].[' + c.name + N'] DROP CONSTRAINT [' + a.name + N'];' AS DropDF
	,4 AS executeDropOrder
	,N'ALTER TABLE [' + d.name + N'].[' + c.name + N'] ADD CONSTRAINT [' + a.name + N'] DEFAULT' + a.definition + N' FOR [' + b.name + N'];' AS CreateDF
FROM sys.default_constraints AS a
	INNER JOIN sys.columns AS b ON b.object_id = a.parent_object_id AND b.column_id = a.parent_column_id
	INNER JOIN sys.tables AS c ON b.object_id = c.object_id
	INNER JOIN sys.schemas AS d ON c.schema_id = d.schema_id;


INSERT INTO #QueryList(executeDropOrder, dropQuery, executeCreateOrder, createQuery)
SELECT
	 2 AS executeDropOrder
	,N'ALTER TABLE [' + d.name + N'].[' + c.name + N'] DROP CONSTRAINT [' + a.name + N'];' AS DropCK
	,3 AS executeDropOrder
	,N'ALTER TABLE [' + d.name + N'].[' + c.name + N'] ADD CONSTRAINT [' + a.name + N'] CHECK' + a.definition + CASE WHEN a.is_not_for_replication = 1 THEN ' NOT FOR REPLICATION' ELSE '' END + N';' AS CreateCK
FROM sys.check_constraints AS a
	INNER JOIN sys.columns AS b ON b.object_id = a.parent_object_id AND b.column_id = a.parent_column_id
	INNER JOIN sys.tables AS c ON b.object_id = c.object_id
	INNER JOIN sys.schemas AS d ON c.schema_id = d.schema_id;


INSERT INTO #QueryList(executeDropOrder, dropQuery, executeCreateOrder, createQuery)
SELECT
	 3 AS executeDropOrder
	,N'ALTER TABLE [' + a.name + N'].[' + b.name + N'] DROP CONSTRAINT [' + c.name + N'];' AS DropFK
	,2 AS executeDropOrder
	,N'ALTER TABLE [' + a.name + N'].[' + b.name + N'] ADD CONSTRAINT [' + c.name + N'] ' + dbo.F_GetForeignKeyColumns(c.object_id) + CASE WHEN c.delete_referential_action_desc = 'NO_ACTION' THEN '' ELSE ' ON DELETE ' + c.delete_referential_action_desc END + CASE WHEN c.update_referential_action_desc = 'NO_ACTION' THEN '' ELSE ' ON UPDATE ' + c.update_referential_action_desc END + CASE WHEN c.is_not_for_replication = 1 THEN ' NOT FOR REPLICATION' ELSE '' END + N';' COLLATE Latin1_General_CI_AS_KS_WS AS CreateFK
FROM sys.schemas AS a
	INNER JOIN sys.tables AS b ON a.schema_id = b.schema_id
	INNER JOIN sys.foreign_keys AS c ON b.object_id = c.parent_object_id
WHERE EXISTS(SELECT TOP 1 '' FROM sys.foreign_key_columns AS d INNER JOIN sys.columns AS e ON d.parent_object_id = e.object_id AND d.parent_column_id = e.column_id WHERE e.collation_name <> @collation_name)
  AND EXISTS(SELECT TOP 1 '' FROM sys.foreign_key_columns AS d INNER JOIN sys.columns AS e ON d.referenced_object_id = e.object_id AND d.referenced_column_id = e.column_id WHERE e.collation_name <> @collation_name);


INSERT INTO #QueryList(executeDropOrder, dropQuery, executeCreateOrder, createQuery)
SELECT DISTINCT
	 4 AS executeDropOrder
	,CASE
		WHEN d.is_primary_key = 0 AND d.is_unique_constraint = 0 THEN N'DROP INDEX [' + d.name + N'] ON [' + f.name + N'].[' + a.name + N'];'
		ELSE N'ALTER TABLE [' + f.name + N'].[' + a.name + N'] DROP CONSTRAINT [' + d.name + N'];'
	END AS DropIndex
	,1 AS executeDropOrder
	,CASE
		WHEN d.is_primary_key = 0 AND d.is_unique_constraint = 0 THEN N'CREATE ' + CASE WHEN d.is_unique = 1 THEN N'UNIQUE ' ELSE '' END + d.type_desc + ' INDEX [' + d.name + N'] ON [' + f.name + N'].[' + a.name + N'](' + dbo.F_GetIndexColumns(d.object_id, d.index_id) + N')' + CASE WHEN d.fill_factor > 0 THEN N' WITH FILLFACTOR = ' + CONVERT(varchar, d.fill_factor) ELSE N'' END + N' ON [' + e.name + N'];'
		ELSE N'ALTER TABLE [' + f.name + N'].[' + a.name + N'] ADD CONSTRAINT [' + d.name + N'] ' + CASE WHEN d.is_primary_key = 1 THEN N'PRIMARY KEY ' WHEN d.is_unique_constraint = 1 THEN N'UNIQUE ' END + d.type_desc + N'(' + dbo.F_GetIndexColumns(d.object_id, d.index_id) + N')' + CASE WHEN d.fill_factor > 0 THEN N' WITH FILLFACTOR = ' + CONVERT(varchar, d.fill_factor) ELSE N'' END + N' ON [' + e.name + N'];'
	END COLLATE Latin1_General_CI_AS_KS_WS AS CreateIndex
FROM sys.tables AS a
	INNER JOIN sys.columns AS b ON a.object_id = b.object_id
	INNER JOIN sys.index_columns AS c ON b.column_id = c.column_id AND b.object_id = c.object_id
	INNER JOIN sys.indexes AS d ON c.object_id = d.object_id AND c.index_id = d.index_id
	INNER JOIN sys.data_spaces AS e ON d.data_space_id = e.data_space_id
	INNER JOIN sys.schemas AS f ON a.schema_id = f.schema_id;






INSERT INTO #AlterCollateQuery(Query)
SELECT dropQuery
FROM #QueryList
ORDER BY executeDropOrder;

INSERT INTO #AlterCollateQuery(Query)
SELECT N'ALTER TABLE [' + a.name + N'].[' + b.name + N'] ALTER COLUMN [' + c.name + N'] ' + d.name + N'(' + CASE WHEN c.max_length < 1 OR c.max_length > 4000 THEN 'MAX' ELSE CONVERT(varchar, c.max_length) END + N') COLLATE ' + @collation_name + CASE WHEN c.is_nullable = 0 THEN N' NOT NULL' ELSE N' NULL' END + N';'
FROM sys.schemas AS a
	INNER JOIN sys.tables AS b ON a.schema_id = b.schema_id
	INNER JOIN sys.columns AS c ON c.object_id = b.object_id
	INNER JOIN sys.types AS d ON d.user_type_id = c.user_type_id
WHERE c.collation_name <> @collation_name;


INSERT INTO #AlterCollateQuery(Query)
SELECT a.Query
FROM
	(
		SELECT N'ALTER DATABASE ' + DB_NAME() + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' AS Query
		UNION ALL SELECT N'ALTER DATABASE ' + DB_NAME() + N' COLLATE ' + @collation_name + ';' AS Query
		UNION ALL SELECT N'ALTER DATABASE ' + DB_NAME() + N' SET MULTI_USER;' AS Query
	) AS a
WHERE EXISTS(SELECT TOP 1 '' FROM sys.databases WHERE name = DB_NAME() AND collation_name <> @collation_name);

INSERT INTO #AlterCollateQuery(Query)
SELECT createQuery
FROM #QueryList
ORDER BY executeCreateOrder;
GO



IF OBJECT_ID('dbo.F_GetForeignKeyColumns', 'FN') IS NOT NULL
	DROP FUNCTION dbo.F_GetForeignKeyColumns;
GO

IF OBJECT_ID('dbo.F_GetIndexColumns', 'FN') IS NOT NULL
	DROP FUNCTION dbo.F_GetIndexColumns;
GO

IF OBJECT_ID('tempdb.dbo.#QueryList', 'U') IS NOT NULL
	DROP TABLE #QueryList;
GO


DECLARE @Query nvarchar(4000);
DECLARE Collate_Query CURSOR FOR
	SELECT a.Query
	FROM #AlterCollateQuery AS a WITH(NOLOCK)
	ORDER BY a.ExecuteOrder;

OPEN Collate_Query;

FETCH Collate_Query INTO @Query;

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @Query;
	EXECUTE sp_executesql @Query;
	
	FETCH Collate_Query INTO @Query;
END;

CLOSE Collate_Query;
DEALLOCATE Collate_Query;
GO


IF OBJECT_ID('tempdb.dbo.#AlterCollateQuery', 'U') IS NOT NULL
	DROP TABLE #AlterCollateQuery;
GO

USE master;
GO

SET NOCOUNT ON;

DECLARE @FilePath nvarchar(4000);
SET @FilePath = '[CSV 파일 경로]';

IF OBJECT_ID('tempdb.dbo.FirstRow', 'U') IS NOT NULL
	DROP TABLE tempdb.dbo.FirstRow;

CREATE TABLE tempdb.dbo.FirstRow
(
	CSV nvarchar(100)
);

DECLARE @Query nvarchar(4000);

SET @Query =
'
BULK INSERT tempdb.dbo.FirstRow FROM ''' + @FilePath + '''
WITH
(
	  FIRSTROW = 1
	 ,LASTROW = 1
);
';

EXECUTE sp_executesql @Query;


SELECT @Query =
'
IF OBJECT_ID(''tempdb.dbo.CSV'', ''U'') IS NOT NULL
	DROP TABLE tempdb.dbo.CSV;
CREATE TABLE tempdb.dbo.CSV
(
	' + REPLACE(CSV, ',', ' nvarchar(4000),') + ' nvarchar(4000)
)'
FROM tempdb.dbo.FirstRow;

EXECUTE sp_executesql @Query;




SET @Query =
'
BULK INSERT tempdb.dbo.CSV FROM ''' + @FilePath + '''
WITH
(
	   FIRSTROW = 2
	  ,FIELDTERMINATOR = '',''
	  ,ROWTERMINATOR = ''\n''
);
';

EXECUTE sp_executesql @Query;


IF OBJECT_ID('tempdb.dbo.#CSV', 'U') IS NOT NULL
	DROP TABLE #CSV;

SELECT *
INTO #CSV
FROM tempdb.dbo.CSV;

IF OBJECT_ID('tempdb.dbo.FirstRow', 'U') IS NOT NULL
	DROP TABLE tempdb.dbo.FirstRow;
IF OBJECT_ID('tempdb.dbo.CSV', 'U') IS NOT NULL
	DROP TABLE tempdb.dbo.CSV;
GO

SELECT * FROM #CSV;

SQL Server 2000 버젼용

SELECT
     SPID
    ,Status
    ,LogiName AS Login
    ,HostName
    ,blocked AS BlkBy
    ,DB_NAME(dbid) AS DBName
    ,cmd AS Command
    ,cpu AS CPUTime
    ,physical_io AS DiskIO
    ,last_batch AS LastBatch
    ,program_name AS ProgramName
    ,DATEDIFF(MI, last_batch, GETDATE())
FROM master..sysprocesses
WHERE SPID <> @@SPID
  AND DATEDIFF(SS, last_batch, GETDATE()) >= 5
  AND (
		   Blocked <> 0
		OR SPID IN
			(
				SELECT blocked
				FROM master..sysprocesses
				WHERE blocked <> 0
				  AND blocked <> @@SPID
			)
	  )
ORDER BY SPID;

SELECT
     a.SPID
    ,a.Status
    ,a.LogiName AS Login
    ,a.HostName
    ,a.Blocked AS BlkBy
    ,DB_NAME(a.dbid) AS DBName
    ,a.Cmd AS Command
    ,a.Cpu AS CPUTime
    ,a.Physical_io AS DiskIO
    ,a.Last_Batch AS LastBatch
    ,a.Program_Name AS ProgramName
    ,b.Text
FROM master.sys.sysprocesses AS a WITH(NOLOCK)
    CROSS APPLY master.sys.dm_exec_sql_text(a.Sql_Handle) AS b
WHERE a.SPID <> @@SPID
  AND
  (
       DATEDIFF(SS, a.Last_Batch, GETDATE()) >= 5
    OR 
	(
		   a.Blocked <> 0
		OR a.SPID IN
			(
				SELECT a.Blocked
				FROM master.sys.sysprocesses
				WHERE a.Blocked <> 0
				  AND a.Blocked <> @@SPID
			)
	)
  )
ORDER BY a.SPID

SELECT
	 a.Session_ID
	,a.Blocking_Session_ID
	,a.Status
	,c.Login_Name
	,c.Host_Name
	,d.client_net_address
	,d.local_tcp_port
	,c.last_request_start_time
	,DB_NAME(a.Database_ID) AS DBName
	,a.Command
	,a.Reads
	,a.Writes
	,a.CPU_Time
	,a.Logical_Reads
	,c.program_name AS ProgramName
	,b.text
FROM master.sys.dm_exec_requests AS a WITH(NOLOCK)
	CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) AS b
	INNER JOIN sys.dm_exec_sessions AS c WITH(NOLOCK) ON a.session_id = c.session_id
	INNER JOIN sys.dm_exec_connections AS d WITH(NOLOCK) ON a.session_id = d.session_id
WHERE a.session_id <> @@SPID
ORDER BY a.session_id

SELECT request_session_id, COUNT(*) num_locks
FROM sys.dm_tran_locks WITH(NOLOCK)
GROUP BY request_session_id
ORDER BY COUNT(*) desc