블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

 « 2024.4  »
일 월 화 수 목 금 토
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30
  • total
  • today
  • yesterday

Linked Server 연결 테스트(TEST)

2018. 10. 26. 21:50 SQL SERVER

DECLARE @Cursor CURSOR
DECLARE @ServerName NVARCHAR( 128)
DECLARE @ServerID INT
DECLARE @SQL VARCHAR( MAX)

--Create temp table to store results
IF object_id (N'tempdb..##LinkedServers') IS NOT NULL
                 DROP TABLE ##LinkedServers

CREATE TABLE ##LinkedServers
                 (
                 [LinkedServerID] INT IDENTITY( 1,1 ) NOT NULL,
                 [Name] SYSNAME NULL,
                 [ProvName] NVARCHAR (128) NULL,
                 [Product] NVARCHAR (128) NULL,
                 [DataSource] NVARCHAR (4000) NULL,
                 [ProvString] NVARCHAR (4000) NULL,
                 [Location] NVARCHAR (4000) NULL,
                 [Cat] SYSNAME NULL
                 )
--Get list of linked servers from system proc
INSERT INTO ##LinkedServers
EXEC [sys] .sp_linkedservers
--Add tested field to result set
ALTER TABLE ##LinkedServers ADD [TestSuccess] BIT

--Cursor over list of linked servers testing each
SET @Cursor = CURSOR FOR
                                                                                                 SELECT
                                                                                                                 [LinkedServerID],
                                                                                                                 [Name]
                                                                                                 FROM
                                                                                                                 ##LinkedServers
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO
                                                                                                                 @ServerID,
                                                                                                                 @ServerName

WHILE (@@FETCH_STATUS = 0 )
BEGIN
                
                 SET @SQL =

                 '
                BEGIN TRY
                                EXEC sp_testlinkedserver [' + @ServerName + ']
                                UPDATE
                                                ##LinkedServers
                                SET
                                                [TestSuccess] = 1
                                WHERE
                                                [LinkedServerID] = ' + CAST(@ServerID AS VARCHAR) + '
                END TRY
                BEGIN CATCH
                                UPDATE
                                                ##LinkedServers
                                SET
                                                [TestSuccess] = 0
                                WHERE
                                                [LinkedServerID] = ' + CAST(@ServerID AS VARCHAR) + '
                END CATCH
                '
                 EXEC(@SQL )

                 FETCH NEXT FROM @Cursor INTO
                                                                                                                                 @ServerID,
                                                                                                                                 @ServerName
END

--Return results
SELECT
                 [Name] AS 'LinkedServerName',
                 [Product],
                 [TestSuccess]
FROM
                 ##LinkedServers

DROP TABLE ##LinkedServers

저작자표시

'SQL SERVER' 카테고리의 다른 글

Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
MDF DATA파일 사용량 조회  (0) 2018.10.26
Find Missing Index  (0) 2018.10.26
서비스팩(SERVICE PACK) 제거  (0) 2018.10.26
posted by bedbmsguru

MDF DATA파일 사용량 조회

2018. 10. 26. 21:49 SQL SERVER

SELECT
       DB_NAME() AS 'DB 명'
       ,d. name AS '파일그룹명'
       ,e. tSize AS '전체사이즈(mb)'
       ,SUM( used_page_count)*8 /1024.0 AS '현재사이즈(mb)'
       ,e. tSize-SUM (used_page_count)* 8 /1024.0 AS '남은사이즈(mb)'
       ,cast(( SUM(used_page_count )*8/ 1024.0)*100 /e. tSize AS DECIMAL( 10,2 ))AS '사용량(%)'
FROM sys .objects AS a   JOIN sys.dm_db_partitiON_stats AS b
ON a .object_id = b .object_id
JOIN  sys .sysindexes AS c
ON a .object_id = c .id
AND b .index_id = c .indid
JOIN sys .filegroups AS d
ON c .groupid = d .data_space_id
JOIN ( SELECT data_space_id ,SUM( size)*8 /1024.0 AS 'tSize'
           FROM sys .databASe_files WHERE data_space_id <> 0 GROUP BY data_space_id) AS e
ON d .data_space_id = e .data_space_id       
GROUP BY d. name,c .groupid, e.tSize

저작자표시

'SQL SERVER' 카테고리의 다른 글

링크드 서버(linked Server)  (0) 2018.10.26
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
Find Missing Index  (0) 2018.10.26
서비스팩(SERVICE PACK) 제거  (0) 2018.10.26
현재 Transaction Log를 사용중인쿼리 확인  (0) 2018.10.26
posted by bedbmsguru

Find Missing Index

2018. 10. 26. 21:49 SQL SERVER

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

저작자표시

'SQL SERVER' 카테고리의 다른 글

Linked Server 연결 테스트(TEST)  (0) 2018.10.26
MDF DATA파일 사용량 조회  (0) 2018.10.26
서비스팩(SERVICE PACK) 제거  (0) 2018.10.26
현재 Transaction Log를 사용중인쿼리 확인  (0) 2018.10.26
SSMS를 이용하여 Excel 데이터 CTRL + V로 DB에 insert 하기  (0) 2018.10.26
posted by bedbmsguru

서비스팩(SERVICE PACK) 제거

2018. 10. 26. 21:47 SQL SERVER


2Active 클러스터환경
  1. SQL SERVER 설치파일준비 (서비스팩 삭제도중에 필요함)
  2. 클러스터 노드 STANDBY로 이동 
  3. appwiz.cpl의 설치된 업데이트에서 SQL SERVER 서비스팩 제거
  4. INSTANCE를 모두 반대편 NODE로 이동
  5. appwiz.cpl의 설치된 업데이트에서 SQL SERVER 서비스팩 제거
  6. Instance를 각각 노드로 재배치

저작자표시

'SQL SERVER' 카테고리의 다른 글

MDF DATA파일 사용량 조회  (0) 2018.10.26
Find Missing Index  (0) 2018.10.26
현재 Transaction Log를 사용중인쿼리 확인  (0) 2018.10.26
SSMS를 이용하여 Excel 데이터 CTRL + V로 DB에 insert 하기  (0) 2018.10.26
최근에 수정된 Object찾기  (0) 2018.10.26
posted by bedbmsguru

현재 Transaction Log를 사용중인쿼리 확인

2018. 10. 26. 21:46 SQL SERVER

-- Purpose: Report active transactions by space or duration.-- Author: I. Stirk.

-- Do not lock anything, and do not get held up by any locks.SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- What SQL statements are currently using the transaction logs?SELECT tst.session_id
  , es.original_login_name
  , DB_NAME(tdt.database_id) AS DatabaseName
  , DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS [TransDuration(s)]
  , tdt.database_transaction_log_record_count AS SpaceUsed
  , CASE tat.transaction_state
      WHEN 0 THEN 'The transaction has not been completely initialized yet'
      WHEN 1 THEN 'The transaction has been initialized but has not started'
      WHEN 2 THEN 'The transaction is active'
      WHEN 3 THEN 'The transaction has ended'
      WHEN 4 THEN 'The commit process has been initiated on the distributed tran'
      WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution'
      WHEN 6 THEN 'The transaction has been committed'
      WHEN 7 THEN 'The transaction is being rolled back'
      WHEN 8 THEN 'The transaction has been rolled back'
      ELSE 'Unknown'
  END AS TransactionState
  , SUBSTRING(TXT.text, ( er.statement_start_offset / 2 ) + 1,
       ( ( CASE WHEN er.statement_end_offset = -1
                     THEN LEN(CONVERT(NVARCHAR(MAX), TXT.text)) * 2
                     ELSE er.statement_end_offset
              END - er.statement_start_offset ) / 2 ) + 1) AS CurrentQuery
  , TXT.text AS ParentQuery
  , es.host_name
  , CASE tat.transaction_type
      WHEN 1 THEN 'Read/Write Transaction'
      WHEN 2 THEN 'Read-Only Transaction'
      WHEN 3 THEN 'System Transaction'
              WHEN 4 THEN 'Distributed Transaction'
              ELSE 'Unknown'
  END AS TransactionType
  , tat.transaction_begin_time AS StartTime
FROM sys.dm_tran_session_transactions AS tst
       INNER JOIN sys.dm_tran_active_transactions AS tat
              ON tst.transaction_id = tat.transaction_id
       INNER JOIN sys.dm_tran_database_transactions AS tdt
              ON tst.transaction_id = tdt.transaction_id
       INNER JOIN sys.dm_exec_sessions es
              ON tst.session_id = es.session_id
       INNER JOIN sys.dm_exec_requests er
              ON tst.session_id = er.session_id
       CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
--ORDER BY tdt.database_transaction_log_record_count DESC -- log space size.ORDER BY [TransDuration(s)] DESC -- transaction duration.

저작자표시

'SQL SERVER' 카테고리의 다른 글

Find Missing Index  (0) 2018.10.26
서비스팩(SERVICE PACK) 제거  (0) 2018.10.26
SSMS를 이용하여 Excel 데이터 CTRL + V로 DB에 insert 하기  (0) 2018.10.26
최근에 수정된 Object찾기  (0) 2018.10.26
user가 가지고 있는 권한 확인  (0) 2018.10.26
posted by bedbmsguru

SSMS를 이용하여 Excel 데이터 CTRL + V로 DB에 insert 하기

2018. 10. 26. 21:44 SQL SERVER

Select the data in excel and press Ctrl + C
  1. Select the data in Excel and press Ctrl + C
  2. In SQL Server Management Studio right click the table and choose Edit Top 200 Rows
  3. Scroll to the bottom and select the entire empty row by clicking on the row header
  4. Paste the data by pressing Ctrl + V

저작자표시

'SQL SERVER' 카테고리의 다른 글

서비스팩(SERVICE PACK) 제거  (0) 2018.10.26
현재 Transaction Log를 사용중인쿼리 확인  (0) 2018.10.26
최근에 수정된 Object찾기  (0) 2018.10.26
user가 가지고 있는 권한 확인  (0) 2018.10.26
Linked server List  (0) 2018.10.26
posted by bedbmsguru

최근에 수정된 Object찾기

2018. 10. 26. 21:34 SQL SERVER

-- 작업할 DB를 저장할 임시테이블
SELECT name as [DatabaseName]
INTO #tmpPubDatabases
FROM sys.databases
WHERE database_id > 4
-- object를 저장할 임시테이블
CREATE TABLE #tmpObjects(
DBName VARCHAR(256),
objectName VARCHAR(256),
obj_desc varchar(100),
create_date datetime,
modify_date datetime
);
DECLARE @command VARCHAR(MAX);
DECLARE @condition tinyint;
SET @condition = 7 --일주일 이내에 변경된 데이터 가져올것
-- 변경된 데이터를 불러서 저장
SET @command = '
USE [?]
IF DB_NAME() IN (SELECT DatabaseName FROM #tmpPubDatabases)
BEGIN
INSERT #tmpObjects
       SELECT db_name() dbname, name objectName, type_desc as obj_desc, create_date,modify_date
FROM sys.objects
WHERE type IN (''TF'' , ''FN'', ''P'', ''V'', ''PK'', ''F'', ''TR'', ''UQ'', ''C'', ''D'', ''U'')
AND modify_date > getdate() - '+ CAST(@condition AS varchar(2)) +'
END';
-- run for all affected databases
EXEC sp_MSforeachdb @command
-- this will match the publications to the tables and give the you row count and sizes
-- run this in the distribution database
SELECT * FROM
#tmpObjects
--#tempObjects 삭제쿼리 넣기
-- drop table #tmpPubDatabases
-- drop table #tmpObjects

저작자표시

'SQL SERVER' 카테고리의 다른 글

현재 Transaction Log를 사용중인쿼리 확인  (0) 2018.10.26
SSMS를 이용하여 Excel 데이터 CTRL + V로 DB에 insert 하기  (0) 2018.10.26
user가 가지고 있는 권한 확인  (0) 2018.10.26
Linked server List  (0) 2018.10.26
파티션(partition) 테이블 현황조회  (0) 2018.10.26
posted by bedbmsguru

user가 가지고 있는 권한 확인

2018. 10. 26. 21:31 SQL SERVER

--user별 권한 확인(특정DB에서만 전체DB검색하는건 아래쪽에 있음)
SELECT
    [DatabaseName] = DB_NAME(),     
       [LoginName] = ulogin.[name],
    [DBUserName] = princ.[name],
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [Role] = null,    
    [PermissionType] = perm. [permission_name],     
    [PermissionState] = perm. [state_desc],     
    [ObjectType] = obj. type_desc,--perm.[class_desc],     
    [ObjectName] = OBJECT_NAME( perm.major_id ),
    [ColumnName] = col. [name]
FROM  
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
--     select top 100 * from sys.login_token
    sys.syslogins ulogin on princ .[sid] = ulogin .[sid]
LEFT JOIN       
    --Permissions
    sys.database_permissions perm ON perm .[grantee_principal_id] = princ .[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col .[object_id] = perm .major_id
                    AND col .[column_id] = perm .[minor_id]
LEFT JOIN
    sys.objects obj ON perm .[major_id] = obj .[object_id]
WHERE
    princ.[type] in ('S', 'U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
       [DatabaseName] = DB_NAME(), 
    [LoginName] = ulogin.[name],
    [DBUserName] = roleprinc.[name],
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [Role] = roleprinc. [name],    
    [PermissionType] = perm. [permission_name],     
    [PermissionState] = perm. [state_desc],     
    [ObjectType] = obj. type_desc,--perm.[class_desc], 
    [ObjectName] = OBJECT_NAME( perm.major_id ),
    [ColumnName] = col. [name]
FROM  
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc .[principal_id] = members .[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc .[principal_id] = members .[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.syslogins ulogin on memberprinc .[sid] = ulogin .[sid]
LEFT JOIN       
    --Permissions
    sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col .[object_id] = perm .major_id
                    AND col .[column_id] = perm .[minor_id]
LEFT JOIN
    sys.objects obj ON perm .[major_id] = obj .[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
       [DatabaseName] = DB_NAME(),
    [LoginName] = '{All Users}',
       [DBUserName] = '{All Users}',
    [UserType] = '{All Users}',
    [Role] = roleprinc. [name],    
    [PermissionType] = perm. [permission_name],     
    [PermissionState] = perm. [state_desc],     
    [ObjectType] = obj. type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME( perm.major_id ),
    [ColumnName] = col. [name]
FROM  
    --Roles
    sys.database_principals roleprinc
LEFT JOIN       
    --Role permissions
    sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col .[object_id] = perm .major_id
                    AND col .[column_id] = perm .[minor_id]                 
JOIN
    --All objects 
    sys.objects obj ON obj .[object_id] = perm .[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name] ,
    OBJECT_NAME(perm .major_id),
    col.[name] ,
    perm.[permission_name] ,
    perm.[state_desc] ,
    obj.type_desc --perm.[class_desc]


--user별 권한 확인(전체DB를 돌면서 진행)
set nocount on
declare @permission table (
Database_Name sysname,
LoginName sysname null,
DBUserName sysname null,
UserType sysname null,
[role] sysname null,
PermissionType sysname null,
PermissionState sysname null,
ObjectType sysname null,
ObjectName sysname null,
ColumnName sysname null
)
declare @dbs table (dbname sysname)
declare @Next sysname
insert into @dbs
select name from sys.databases order by name
select top 1 @Next = dbname from @dbs
while (@@rowcount<>0)
begin
insert into @permission
exec('use [' + @Next + ']
SELECT
    [DatabaseName] = DB_NAME(),     
       [LoginName] = ulogin.[name],
    [DBUserName] = princ.[name],
    [UserType] = CASE princ.[type]
                    WHEN ''S'' THEN ''SQL User''
                    WHEN ''U'' THEN ''Windows User''
                 END,
    [Role] = null,    
    [PermissionType] = perm. [permission_name],     
    [PermissionState] = perm. [state_desc],     
    [ObjectType] = obj. type_desc,--perm.[class_desc],     
    [ObjectName] = OBJECT_NAME( perm.major_id ),
    [ColumnName] = col. [name]
FROM  
    sys.database_principals princ
LEFT JOIN
    sys.syslogins ulogin on princ .[sid] = ulogin .[sid]
LEFT JOIN       
    --Permissions
    sys.database_permissions perm ON perm .[grantee_principal_id] = princ .[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col .[object_id] = perm .major_id
                    AND col .[column_id] = perm .[minor_id]
LEFT JOIN
    sys.objects obj ON perm .[major_id] = obj .[object_id]
WHERE
    princ.[type] in (''S'', ''U'')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
       [DatabaseName] = DB_NAME(), 
    [LoginName] = ulogin.[name],
    [DBUserName] = roleprinc.[name],
    [UserType] = CASE memberprinc.[type]
                    WHEN ''S'' THEN ''SQL User''
                    WHEN ''U'' THEN ''Windows User''
                 END,
    [Role] = roleprinc. [name],    
    [PermissionType] = perm. [permission_name],     
    [PermissionState] = perm. [state_desc],     
    [ObjectType] = obj. type_desc,--perm.[class_desc], 
    [ObjectName] = OBJECT_NAME( perm.major_id ),
    [ColumnName] = col. [name]
FROM  
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc .[principal_id] = members .[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc .[principal_id] = members .[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.syslogins ulogin on memberprinc .[sid] = ulogin .[sid]
LEFT JOIN       
    --Permissions
    sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col .[object_id] = perm .major_id
                    AND col .[column_id] = perm .[minor_id]
LEFT JOIN
    sys.objects obj ON perm .[major_id] = obj .[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
       [DatabaseName] = DB_NAME(),
    [LoginName] = ''{All Users}'',
       [DBUserName] = ''{All Users}'',
    [UserType] = ''{All Users}'',
    [Role] = roleprinc. [name],    
    [PermissionType] = perm. [permission_name],     
    [PermissionState] = perm. [state_desc],     
    [ObjectType] = obj. type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME( perm.major_id ),
    [ColumnName] = col. [name]
FROM  
    --Roles
    sys.database_principals roleprinc
LEFT JOIN       
    --Role permissions
    sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col .[object_id] = perm .major_id
                    AND col .[column_id] = perm .[minor_id]                 
JOIN
    --All objects 
    sys.objects obj ON obj .[object_id] = perm .[major_id]
WHERE
    --Only roles
    roleprinc.[type] = ''R'' AND
    --Only public role
    roleprinc.[name] = ''public'' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name] ,
    OBJECT_NAME(perm .major_id),
    col.[name] ,
    perm.[permission_name] ,
    perm.[state_desc] ,
    obj.type_desc --perm.[class_desc]'
)
delete @dbs where dbname = @Next
select top 1 @Next = dbname from @dbs
end
set nocount off
select * from @permission



저작자표시

'SQL SERVER' 카테고리의 다른 글

SSMS를 이용하여 Excel 데이터 CTRL + V로 DB에 insert 하기  (0) 2018.10.26
최근에 수정된 Object찾기  (0) 2018.10.26
Linked server List  (0) 2018.10.26
파티션(partition) 테이블 현황조회  (0) 2018.10.26
SSMS의 Tip  (0) 2018.10.26
posted by bedbmsguru

Linked server List

2018. 10. 26. 21:30 SQL SERVER

Linked Server 조회

SELECT ss.server_id 
          ,ss.name 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
                            end 
          ,ss.product 
          ,ss.provider 
          ,ss.catalog 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
                                   end 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
                                    end 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id

저작자표시

'SQL SERVER' 카테고리의 다른 글

최근에 수정된 Object찾기  (0) 2018.10.26
user가 가지고 있는 권한 확인  (0) 2018.10.26
파티션(partition) 테이블 현황조회  (0) 2018.10.26
SSMS의 Tip  (0) 2018.10.26
Index REBUILD OR REORG 쿼리  (0) 2018.10.26
posted by bedbmsguru

파티션(partition) 테이블 현황조회

2018. 10. 26. 21:30 SQL SERVER

SELECT t.name AS TableName, i.name AS IndexName , p.partition_number, e.name, f.name, f.type_desc, p.rows, rv.value, 
CASE WHEN f.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'Right' END AS LeftRightBoundary,
CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A' 
ELSE
    CASE WHEN boundary_value_on_right = 0 AND rv2.value IS NULL THEN '>=' 
        WHEN boundary_value_on_right = 0 THEN '>' 
        ELSE '>=' 
    END + ' ' + ISNULL(CONVERT(varchar(50), rv2.value, 121), 'Min Value') + ' ' + 
        CASE boundary_value_on_right WHEN 1 THEN 'and <' 
                ELSE 'and <=' END 
        + ' ' + ISNULL(CONVERT(varchar(50), rv.value, 121), 'Max Value') 
END AS TextComparison
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
    ON i.object_id = p.object_id AND i.index_id = p.index_id   
JOIN (select bb.name, aa.destination_id from sys.destination_data_spaces aa inner join sys.data_spaces bb ON aa.data_space_id=bb.data_space_id) E
ON p.partition_number = e.destination_id
JOIN  sys.partition_schemes AS s   
    ON i.data_space_id = s.data_space_id  
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  
LEFT JOIN sys.partition_range_values AS rv
    ON f.function_id = rv.function_id
    AND p.partition_number = rv.boundary_id     
LEFT JOIN sys.partition_range_values AS rv2
    ON f.function_id = rv2.function_id
    AND p.partition_number - 1= rv2.boundary_id
WHERE i.type <= 1 AND t.name = 'LOGIN_INFO_REPOSITORY'
ORDER BY t.name, p.partition_number;

저작자표시

'SQL SERVER' 카테고리의 다른 글

user가 가지고 있는 권한 확인  (0) 2018.10.26
Linked server List  (0) 2018.10.26
SSMS의 Tip  (0) 2018.10.26
Index REBUILD OR REORG 쿼리  (0) 2018.10.26
알아봐요 할 DMV  (0) 2018.09.19
posted by bedbmsguru

티스토리툴바