Drawing the world RSS FEED

--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

Identity 컬럼이 없던 테이블에 Identity컬럼을 추가 할 경우

임의의(의도치 않은) 순서로 번호가 매겨지게 된다

이를 특정 순서로 정렬하기 위해 여러 테스트를 하던 도중

SELECT * FROM [대상테이블]

매번 할 때와 같은 순서로 번호가 매겨지는걸 발견했다.

따라서 Identity 컬럼을 추가하기 전에 Order by를 하지 않고도 원하는 순서로 정렬되어 출력하도록 하면 되는데

이를 유도하기 위해 해당 테이블에 모든 제약조건이나 인덱스를 삭제하고 원하는 정렬 순서로 Clustered 인덱스를 만들게 되면

(Clustered Index는 테이블내 데이터를 미리 정렬해 둠으로)

Order by없이 정렬된 순서로 출력되기를 유도 할 수 있게 된다(어디까지나 유도이기 때문에 전, 후 확인이 필요하다)

작업 후에 지웠던 제약조건이나 인덱스를 만들고 확인만 하면 끝!

다음은 예제 쿼리

--정렬된 Identity 컬럼 추가
If Object_ID('dbo.Test') Is Not Null
    Drop Table dbo.Test
Go


Create Table dbo.Test
(
     Data1 Int
    ,Data2 Int
    ,Data3 Int
)
Go


Insert Into dbo.Test(Data1, Data2, Data3) Values(43, 123, 42)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(23, 5, 52)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(154, 12, 11)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(23, 23, 23)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(42, 12, 53)
Go

Insert Into dbo.Test(Data1, Data2, Data3) Values(533, 21, 521)
Go


--기존에 인덱스가 있다면 인덱스를 삭제하고 원하는 순서로 인덱스 생성하고 작업 완료후 인덱스를 정리
Create Clustered Index IDX_Test_Data1_Data2_Data3 On dbo.Test(Data1 Asc, Data2 Asc, Data3 Asc)
Go


Alter Table dbo.Test Add A Int Identity(1,1)
Go

Select *
From dbo.Test
   
Select *
From
(
    Select *,ROW_NUMBER() Over (Order By Data1 Asc, Data2 Asc, Data3 Asc) As B
    From dbo.Test
) As Test
Where A<>B
Order By A Asc,B Asc
Go


If Object_ID('dbo.Test') Is Not Null
    Drop Table dbo.Test
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