보미네

Partition

PartitionScheme (function name)

PartitionCount 

FileGroup

Schema Binding indexed View

Include 

ColumnStore 

Unique

UniqueConstraint

Primarykey

Filtered Index Definition

Disabled

IndexSize

index stat 

physical stat

statistics update date

쓰는 인덱스 인지 안 쓰는 인덱스 인지 뭐 정보가 좀 필요할듯 해 만들었습니다. 

치훈이가 이름 붙여줌...

믿고쓰는 김장애 선생님의 sp_helpindex2 

장애나면 연락 하세요~ ^.^~

-- 자 갑니다. ~

STAT3 은 주석 처리 하세요~ 

용량 큰 테이블이 있으면 오래 걸립니다. ~

sql 2014

IF OBJECT_ID('SP_HELPINDEX2') IS NULL
	EXEC ('CREATE PROC SP_HELPINDEX2 AS SELECT 1 ')
GO

ALTER PROCEDURE SP_HELPINDEX2 @NAME NVARCHAR(4000) = ''
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- SCRIPT BY MINSOUK KIM 
-- 01099670955
-- A.TO.Z@NAVERCORP.COM
SELECT ISNULL(PS.TYPE_DESC, 'NORMAL') AS ISPTN
	,PS.NAME AS PTNFUNCNAME
	,D.PARTITIONCNT AS PTNCNT
	,T1.TYPE_DESC
	,FI.NAME AS FGNAME
	,SCHEMA_NAME(SCHEMA_ID) AS SNAME
	,T1.CREATE_DATE AS TBLCREDATE
	,T1.NAME AS OBJNAME
	,I.TYPE_DESC ISCLUSTERORHEAP
	,I.NAME AS IXNAME
	,INDEXSIZEKB AS IXSIZEKB
	,SUBSTRING(B.INDEX_COLUMN_DESC, 3, 1000) AS IXDESC
	,SUBSTRING(C.INCLUDE_COLUMN_DESC, 3, 1000) AS INCOLDESC
	,STATS_DATE(i.object_id, i.index_id) AS [STATISTICUPDATEDATE]
	,I.IS_UNIQUE
	,I.IS_UNIQUE_CONSTRAINT
	,I.IS_PRIMARY_KEY
	,I.HAS_FILTER
	,I.FILTER_DEFINITION
	,I.IS_DISABLED
	,STAT1.*
	,STAT2.*
--,STAT3.*
--	, * 
FROM SYS.OBJECTS AS T1
INNER JOIN SYS.INDEXES AS I ON T1.[OBJECT_ID] = I.[OBJECT_ID]
LEFT JOIN SYS.FILEGROUPS FI ON I.DATA_SPACE_ID = FI.DATA_SPACE_ID
--    AND I.[TYPE] IN (0,1) 
LEFT JOIN SYS.PARTITION_SCHEMES PS ON I.DATA_SPACE_ID = PS.DATA_SPACE_ID
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME + CASE 
			WHEN IS_DESCENDING_KEY = 1
				THEN ' DESC'
			ELSE ' ASC'
			END AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 0
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) B(INDEX_COLUMN_DESC)
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 1
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) C(INCLUDE_COLUMN_DESC)
OUTER APPLY (
	SELECT COUNT(*)
	FROM SYS.TABLES AS T
	INNER JOIN SYS.INDEXES AS I ON T.OBJECT_ID = I.OBJECT_ID
	INNER JOIN SYS.PARTITIONS AS P ON I.OBJECT_ID = P.OBJECT_ID
		AND I.INDEX_ID = P.INDEX_ID
	INNER JOIN SYS.PARTITION_SCHEMES AS S ON I.DATA_SPACE_ID = S.DATA_SPACE_ID
	INNER JOIN SYS.PARTITION_FUNCTIONS AS F ON S.FUNCTION_ID = F.FUNCTION_ID
	LEFT JOIN SYS.PARTITION_RANGE_VALUES AS R ON F.FUNCTION_ID = R.FUNCTION_ID
		AND R.BOUNDARY_ID = P.PARTITION_NUMBER
	WHERE T.NAME = T1.NAME
		AND I.TYPE <= 1
	) D(PARTITIONCNT)
OUTER APPLY (
	SELECT 8 * SUM(A.USED_PAGES) AS 'INDEXSIZE(KB)'
	FROM SYS.INDEXES AS III
	INNER JOIN SYS.PARTITIONS AS P ON P.OBJECT_ID = III.OBJECT_ID
		AND P.INDEX_ID = I.INDEX_ID
	INNER JOIN SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID
	WHERE III.OBJECT_ID = T1.OBJECT_ID
		AND III.INDEX_ID = I.INDEX_ID
	) E(INDEXSIZEKB)
OUTER APPLY (
	SELECT TOP 1 *
	FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL)
	WHERE 1 = CASE 
			WHEN T1.TYPE_DESC = 'SQL_TABLE_VALUED_FUNCTION'
				THEN 0
			ELSE 1
			END
	) STAT2
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.DM_DB_INDEX_USAGE_STATS
		) A
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID
                    AND DATABASE_ID = DB_ID()
	) STAT1
OUTER APPLY (
	SELECT TOP 1 *
	FROM SYS.dm_db_index_physical_stats(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL, NULL)
	) STAT3
WHERE IS_MS_SHIPPED = 0
	AND T1.NAME = CASE 
		WHEN @NAME = ''
			THEN T1.NAME
		ELSE @NAME
		END
ORDER BY 1
	,2
	,3
	,4
	,5
	,6
	,7
	,I.INDEX_ID
GO

EXEC sp_helpindex2
GO

2008 r2

IF OBJECT_ID('SP_HELPINDEX2') IS NULL
	EXEC ('CREATE PROC SP_HELPINDEX2 AS SELECT 1 ')
GO

ALTER PROCEDURE SP_HELPINDEX2 @NAME NVARCHAR(4000) = ''
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- SCRIPT BY MINSOUK KIM 
-- 01099670955
-- A.TO.Z@NAVERCORP.COM
SELECT ISNULL(PS.TYPE_DESC, 'NORMAL') AS ISPTN
	, PS.NAME AS PTNFUNCNAME
	, D.PARTITIONCNT AS PTNCNT
	, T1.TYPE_DESC
	, FI.NAME AS FGNAME
	, SCHEMA_NAME(SCHEMA_ID) AS SNAME
	, T1.CREATE_DATE AS TBLCREDATE
	, T1.NAME AS OBJNAME
	, I.TYPE_DESC ISCLUSTERORHEAP
	, I.NAME AS IXNAME
	, INDEXSIZEKB AS IXSIZEKB
	, SUBSTRING(B.INDEX_COLUMN_DESC, 3, 1000) AS IXDESC
	, SUBSTRING(C.INCLUDE_COLUMN_DESC, 3, 1000) AS INCOLDESC
	, STATS_DATE(i.object_id, i.index_id) AS [STATISTICUPDATEDATE]
	, I.IS_UNIQUE
	, I.IS_UNIQUE_CONSTRAINT
	, I.IS_PRIMARY_KEY
	, I.HAS_FILTER
	, I.FILTER_DEFINITION
	, I.IS_DISABLED
	, STAT1.*
	, STAT2.*
--,STAT3.*
--	, * 
FROM SYS.OBJECTS AS T1
INNER JOIN SYS.INDEXES AS I ON T1.[OBJECT_ID] = I.[OBJECT_ID]
LEFT JOIN SYS.FILEGROUPS FI ON I.DATA_SPACE_ID = FI.DATA_SPACE_ID
--    AND I.[TYPE] IN (0,1) 
LEFT JOIN SYS.PARTITION_SCHEMES PS ON I.DATA_SPACE_ID = PS.DATA_SPACE_ID
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME + CASE 
			WHEN IS_DESCENDING_KEY = 1
				THEN ' DESC'
			ELSE ' ASC'
			END AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 0
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) B(INDEX_COLUMN_DESC)
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 1
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) C(INCLUDE_COLUMN_DESC)
OUTER APPLY (
	SELECT COUNT(*)
	FROM SYS.TABLES AS T
	INNER JOIN SYS.INDEXES AS I ON T.OBJECT_ID = I.OBJECT_ID
	INNER JOIN SYS.PARTITIONS AS P ON I.OBJECT_ID = P.OBJECT_ID
		AND I.INDEX_ID = P.INDEX_ID
	INNER JOIN SYS.PARTITION_SCHEMES AS S ON I.DATA_SPACE_ID = S.DATA_SPACE_ID
	INNER JOIN SYS.PARTITION_FUNCTIONS AS F ON S.FUNCTION_ID = F.FUNCTION_ID
	LEFT JOIN SYS.PARTITION_RANGE_VALUES AS R ON F.FUNCTION_ID = R.FUNCTION_ID
		AND R.BOUNDARY_ID = P.PARTITION_NUMBER
	WHERE T.NAME = T1.NAME
		AND I.TYPE <= 1
	) D(PARTITIONCNT)
OUTER APPLY (
	SELECT 8 * SUM(A.USED_PAGES) AS 'INDEXSIZE(KB)'
	FROM SYS.INDEXES AS III
	INNER JOIN SYS.PARTITIONS AS P ON P.OBJECT_ID = III.OBJECT_ID
		AND P.INDEX_ID = I.INDEX_ID
	INNER JOIN SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID
	WHERE III.OBJECT_ID = T1.OBJECT_ID
		AND III.INDEX_ID = I.INDEX_ID
	) E(INDEXSIZEKB)
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(), NULL, NULL, NULL)
		) a
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID
		AND 1 = CASE 
			WHEN T1.TYPE_DESC = 'SQL_TABLE_VALUED_FUNCTION'
				THEN 0
			ELSE 1
			END
	) STAT2
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.DM_DB_INDEX_USAGE_STATS
		) A
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID
		AND DATABASE_ID = DB_ID()
	) STAT1
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
		) a
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID
		AND 1 = CASE 
			WHEN T1.TYPE_DESC = 'SQL_TABLE_VALUED_FUNCTION'
				THEN 0
			ELSE 1
			END
	) STAT3
WHERE IS_MS_SHIPPED = 0
	AND T1.NAME = CASE 
		WHEN @NAME = ''
			THEN T1.NAME
		ELSE @NAME
		END
ORDER BY 1
	, 2
	, 3
	, 4
	, 5
	, 6
	, 7
	, I.INDEX_ID
GO

EXEC sp_helpindex2
GO

드라이버 32, 64 둘 다 깔고

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'BOA', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'BOA', @provstr=N'BOA', @catalog=N'dbname'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BOA',@useself=N'False',@locallogin=NULL,@rmtuser=N'',@rmtpassword=''

GO
EXEC master.dbo.sp_addlinkedserver @server = N'wmman', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'wmman', @provstr=N'wmman', @catalog=N'wmman'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'wmman',@useself=N'False',@locallogin=NULL,@rmtuser=N'id',@rmtpassword='pass'

select * from openquery(wmman,'select * from tb_ticket')

alter proc usp_loader
(
@sourceFolderName nvarchar(4000)
, @filename nvarchar(4000)
)
as

if object_id ('tempdb..##tMdsApiResultsTemp') is not null
drop table ##tMdsApiResultsTemp

create table ##tMdsApiResultsTemp
(cIp varchar(100)
,cApi varchar(100)
,cVersion varchar(100)
,cLastAccess varchar(100)
)

declare @sql nvarchar(4000)
=
'
BULK INSERT ##tMdsApiResultsTemp
FROM '''+@sourceFolderName+@fileName+'''
WITH
(
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''0x0a'',
TABLOCK
)
'

print (@sql)
exec (@sql)

insert into tMdsApiResults (cFilename, cIp, cApiComponent, cApiPlatform, cVersion, cLastAccess)
select
@filename
, cIp
, substring(cApi, 1, CHARINDEX(':', cApi)-1)
, substring(cApi, CHARINDEX(':', cApi)+1, len(cApi))
, cVersion
, cLastAccess
from ##tMdsApiResultsTemp
go

과거에는 아래 방법으로 많이 했는데, 요즘은 CLR 로 하면 더 멋지게 가능할 듯 한데.....

언제 함 만들어 봐야겠다.....예전에 내가 만든건 어디 있는지도 모르겠네...쩝...

원문 : http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

http://ceusee.tistory.com/188

동적으로 PIVOT을 만들 수 있는 방법입니다.

아직도 case 문이 익숙하긴 하지만, 가끔은 오히려 더 복잡해지는 case 문 때문에 PIVOT을 사용해야 한다고 느껴질 때가 있습니다.

아직 PIVOT 익숙하지는 않은데 하다보니 컬럼 고정이 되서 불편한 점이 있는데 이 방법으로 사용하면 좋을 것 같네요.

PIVOT 은 행 형태의 반환을 열로 만들어 주는 것입니다.

아래로 테스트할 데이터를 생성 합니다.

결과는 아래 처럼 행으로 나 옵니다.  그러나 열로 표시하고 싶어요. 하면 PIVOT 처리 합니다.

원하는 결과가 나왔습니다. ~

다른 데이터를 추가 해 보죠.

그리고 나서 다시 윗 쿼리를 실행 해 보면 결과는 동일하게 3개의 데이터의 집계만 나타납니다.

당연한 결과 입니다. 지금 추가된 blat는 보이지 않습니다. PIVOT는 집계해서 보여줄 컬럼을 명시해야 하기 때문입니다.   쿼리를 그럼 고쳐야 하는데 좀 더 쉽게 동적으로 할 수 있게 아래는 도와 줍니다.

원하는 결과가 도출.