블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

 « 2024.11  »
일 월 화 수 목 금 토
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

최근에 수정된 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

SSMS의 Tip

2018. 10. 26. 21:27 SQL SERVER

     1.Go
       Batch의 끝을 나타냄 Go 뒤에 숫자를 붙일 경우 숫자 만큼 실행됨


  1. 유용한 단축키
 
단축키용도
Ctrl + UDB변경
Ctrl + R결과창을 Toggle
Ctrl + ]매칭되는 괄호찾기
Ctrl + K, Ctrl + C현재행 주석처리
Ctrl + K, Ctrl + U현재행 주석풀기
Shift + Alt + EnterCode Editor 를 Full Screen
Shift + Alt + 화살표키
여러줄에 동시코딩할때 여러줄에 한번에 ' 넣기 등 
Ctrl + ICode Editor 내의 Quick Search


  1. 열(Column)전체를 Code Editor로 내보내기
     - 테이블 리스트의 Object Explorer 의 [열](column) 항목을 Code Editor로 Drag&Drop


  1. Query window 에 Line Number 추가하기
      

  1. Alt 키를 누른상태에서 텍스트 선택하기 삭제해야 될 블럭을 커서가 있는 기준으로 선택 가능





저작자표시

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

Linked server List  (0) 2018.10.26
파티션(partition) 테이블 현황조회  (0) 2018.10.26
Index REBUILD OR REORG 쿼리  (0) 2018.10.26
알아봐요 할 DMV  (0) 2018.09.19
Troubleshooting transactional replication latency issues  (0) 2018.08.10
posted by bedbmsguru

Index REBUILD OR REORG 쿼리

2018. 10. 26. 21:26 SQL SERVER

SELECT  OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
        i.name AS [IndexName] ,
        i.index_id ,
        user_seeks + user_scans + user_lookups AS [Reads] ,
        user_updates AS [Writes] ,
              [Indexsize(MB)],
              TB_SIZE.[Tablesize(MB)],
        i.type_desc AS [IndexType] ,
        i.fill_factor AS [FillFactor],
              st.avg_fragmentation_in_percent,
              case when st.avg_fragmentation_in_percent > 30 then
                     'alter index ' + i.name + ' on ' + OBJECT_NAME(s.[object_id]) + ' rebuild with (online = on)'
              else
                     'alter index ' + i.name + ' on ' + OBJECT_NAME(s.[object_id]) + ' REORGANIZE'
              end AS QUERY
FROM    sys.dm_db_index_usage_stats AS s
        INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
              cross apply sys.dm_db_index_physical_stats(db_id(),s.[object_id], i.index_id, null, null) ST
              INNER JOIN
              (
               SELECT
                     i.OBJECT_ID,
                     OBJECT_NAME(i.OBJECT_ID) AS TableName,
                     i.name AS IndexName,
                     i.index_id AS IndexID,
                     8 * SUM(a .used_pages) / 1024 AS 'Indexsize(MB)'
              FROM sys .indexes AS i
                     JOIN sys .partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
                     JOIN sys .allocation_units AS a ON a.container_id = p.partition_id
                     --where OBJECT_NAME (i. OBJECT_ID) ='TLogState'
                     GROUP BY i. OBJECT_ID,i .index_id, i.name
              )AS IND_SIZE
              ON i.object_id = IND_SIZE.object_id
              AND i.index_id = IND_SIZE.IndexID
              INNER JOIN
              (
                     
                     SELECT
                           i.object_id,
                           SUM(a .total_pages) * 8 /1024 AS 'Tablesize(MB)'
                     FROM  sys. indexes i
                     INNER JOIN sys. partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
                     INNER JOIN sys. allocation_units a ON p.partition_id = a.container_id
                     WHERE i.OBJECT_ID > 255
                     GROUP BY i.object_id
              )AS TB_SIZE
              ON TB_SIZE.object_id = I.object_id
WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
        AND i.index_id = s.index_id
        AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) ,
        writes DESC ,
        reads DESC ;

저작자표시

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

파티션(partition) 테이블 현황조회  (0) 2018.10.26
SSMS의 Tip  (0) 2018.10.26
알아봐요 할 DMV  (0) 2018.09.19
Troubleshooting transactional replication latency issues  (0) 2018.08.10
Replication Subscription Type 확인  (0) 2018.05.09
posted by bedbmsguru

DML쿼리 튜닝방법

2018. 9. 19. 16:31 오라클

DBMS Call을 줄인다.

 (1) 절차적 Loop를 One SQL로 변경한다.

 (2) Array Processing 활용 Array Size를 크게 늘려서 DBMS Call을 줄인다.

 (3) Index 및 제약조건 해제를 통하여 대량 DML 성능 개선

저작자표시

'오라클' 카테고리의 다른 글

오라클 Object DDL 스크립트 추출  (0) 2019.03.06
오라클 세션별 CPU, PGA 사용량 확인 쿼리  (0) 2018.12.31
스케줄러 사용법  (0) 2018.08.27
특정테이블 index Size 조회  (0) 2018.08.13
아무 문제가 없이 등록된 Job이 실행되지 않을때  (0) 2018.07.04
posted by bedbmsguru

알아봐요 할 DMV

2018. 9. 19. 13:07 SQL SERVER


https://www.mssqltips.com/sqlservertutorial/273/dynamic-management-views/

저작자표시

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

SSMS의 Tip  (0) 2018.10.26
Index REBUILD OR REORG 쿼리  (0) 2018.10.26
Troubleshooting transactional replication latency issues  (0) 2018.08.10
Replication Subscription Type 확인  (0) 2018.05.09
Backup 및 Restore 진행현황 쿼리  (0) 2017.11.08
posted by bedbmsguru

오라빅스 TableSpace 모니터링 쿼리 수정

2018. 9. 13. 17:33 Zabbix

system 임시 테이블 스페이스 제외하고 사용자 테이블스페이스만 모니터링 되도록 query.property 수정




select * from
(
select '- Tablespace ->', substr(a.tablespace_name,1,30) TABLESPACE_NAME,
'- TOTAL_SIZE_MB ->', round(sum(a.total1)/1024/1024,1) TOTAL_SIZE_MB ,
'- USED_SIZE_MB ->', round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) USED_SIZE_MB,
'- FREE_SIZE_MB ->', round(sum(a.sum1)/1024/1024,1) FREE_SIZE_MB,
'- USED_PERCENT ->', round(100 - (round(sum(a.sum1)/1024/1024,1) / round(sum(a.total1)/1024/1024,1) * 100), 1) USED_PERCENT
from
(select tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
from dba_free_space
WHERE tablespace_name NOT IN('SYSTEM', 'SYSAUX', 'UNDOTBS1')
group by tablespace_name
union
select tablespace_name,sum(bytes) total1,0,0,0
from dba_data_files
WHERE tablespace_name NOT IN('SYSTEM', 'SYSAUX', 'UNDOTBS1')
group by tablespace_name) a
group by a.tablespace_name
) where USED_PERCENT > 93 

저작자표시

'Zabbix' 카테고리의 다른 글

SAN Switch 모니터링 적용  (0) 2018.10.26
brocade SAN fc port zabbix 모니터링  (0) 2018.10.26
Zabbix Linux Disk IO 모니터링  (0) 2018.09.07
zabbix로 mariadb, mysql 모니터링 적용  (0) 2018.08.28
zabbix Trigger Flapping 방지  (0) 2018.07.05
posted by bedbmsguru

Zabbix Linux Disk IO 모니터링

2018. 9. 7. 15:48 Zabbix

IO 모니터링


https://github.com/grundic/zabbix-disk-performance

저작자표시

'Zabbix' 카테고리의 다른 글

brocade SAN fc port zabbix 모니터링  (0) 2018.10.26
오라빅스 TableSpace 모니터링 쿼리 수정  (0) 2018.09.13
zabbix로 mariadb, mysql 모니터링 적용  (0) 2018.08.28
zabbix Trigger Flapping 방지  (0) 2018.07.05
zabbix_proxy - cannot connect [13] permission denied 해결  (0) 2018.07.04
posted by bedbmsguru

티스토리툴바