블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

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
31
  • total
  • today
  • yesterday
2022. 11. 8. 11:07 SQL SERVER

SELECT 
     [object_id]        = t.[object_id]
    ,[schema_name]      = s.[name]
    ,[table_name]       = t.[name]
    ,[index_name]       = CASE WHEN i.[type] in (0,1,5) THEN null    ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore
    ,[object_type]      = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX'  END
    ,[index_type]       = i.[type_desc]
    ,[partition_count]  = p.partition_count
    ,[row_count]        = p.[rows]
    ,[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'
                               ELSE (  SELECT DISTINCT p.data_compression_desc
                                       FROM sys.partitions p
                                       WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id
                                    )
                          END
    ,[total_space_MB]   = cast(round(( au.total_pages                  * (8/1024.00)), 2) AS DECIMAL(36,2))
    ,[used_space_MB]    = cast(round(( au.used_pages                   * (8/1024.00)), 2) AS DECIMAL(36,2))
    ,[unused_space_MB]  = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables  t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN (
    SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])
    FROM sys.partitions
    GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN (
    SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)
    FROM sys.partitions p
    JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]
    GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table

 

https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

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

Agent Job 실패확인  (0) 2023.09.11
synonym의 base object 조회  (0) 2022.12.15
SQL 실행횟수 모니터링  (0) 2022.03.15
DB 이관시 설정상태 체크, 변경  (0) 2022.02.07
Lock Or Expired된 계정 찾기  (0) 2021.11.25
posted by bedbmsguru
2022. 3. 15. 16:24 SQL SERVER

튜닝대상 선정을 위한 SQL 실행횟수 확인

 

SELECT  s.totalexecutioncount,
DB_NAME(t.dbid) daname, 
        t.text,
        s.TotalExecutionCount,
        s.TotalElapsedTime,
        s.TotalLogicalReads,
        s.TotalPhysicalReads
FROM    (SELECT deqs.plan_handle,
                SUM(deqs.execution_count) AS TotalExecutionCount,
                SUM(deqs.total_elapsed_time) AS TotalElapsedTime,
                SUM(deqs.total_logical_reads) AS TotalLogicalReads,
                SUM(deqs.total_physical_reads) AS TotalPhysicalReads
         FROM   sys.dm_exec_query_stats AS deqs
         GROUP BY deqs.plan_handle
        ) AS s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
ORDER BY s.TotalLogicalReads DESC ;

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

synonym의 base object 조회  (0) 2022.12.15
전체Table Size 확인  (0) 2022.11.08
DB 이관시 설정상태 체크, 변경  (0) 2022.02.07
Lock Or Expired된 계정 찾기  (0) 2021.11.25
Examine IO subsystem latencies IN SQL SERVER  (0) 2021.11.24
posted by bedbmsguru
2022. 2. 18. 15:32 공부용 링크

나도 하루에 하나씩.

 

https://sqlservergeeks.com/sql-server-dmv/

 

SQL Server DMV Series - SQLServerGeeks.com

SQL Server DMV series from Manohar Punna, brought to you by SQLServerGeeks.com

sqlservergeeks.com

 

 

'공부용 링크' 카테고리의 다른 글

SQL서버 관련 링크  (0) 2013.11.11
posted by bedbmsguru
2022. 2. 7. 15:24 SQL SERVER

restore filelistonly from disk='H:\temp\DBNAME_FULL2.BAK'

restore database DBNAME from disk='H:\temp\DBNAME_FULL2.bak'
with move 'TEST_Data' to 'K:\SQL_DATA\DBNAME.MDF',
move 'TEST_Data_Log' to 'N:\LOG_DATA\DBNAME_log.LDF'


--논리적이름 변경
ALTER DATABASE DBNAME MODIFY FILE ( NAME = 과거이름_Data, NEWNAME = DBNAME );
GO
ALTER DATABASE DBNAME MODIFY FILE ( NAME = 과거이름_Log, NEWNAME = DBNAME_LOG );
GO

--자동증가 변경
ALTER DATABASE DBNAME
MODIFY FILE ( NAME = 'DBNAME', FILEGROWTH = 128MB )
GO
ALTER DATABASE DBNAME
MODIFY FILE ( NAME = 'DBNAME_log', FILEGROWTH = 64MB )
GO

--vlf설정 최적화(가능하다면)
dbcc loginfo('DBNAME')
ALTER DATABASE DBNAME SET RECOVERY simple

use DBNAME
dbcc shrinkfile ('DBNAME_log', 0, TRUNCATEONLY) --로그파일 갯수를 줄인다.

ALTER DATABASE DBNAME MODIFY FILE (NAME = N'DBNAME_log' , SIZE = 256MB);  --로그파일 사이즈를

ALTER DATABASE DBNAME SET RECOVERY FULL

BACKUP DATABASE DBNAME TO DISK='d:\BDBNAME_FULL.BAK' WITH INIT

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

전체Table Size 확인  (0) 2022.11.08
SQL 실행횟수 모니터링  (0) 2022.03.15
Lock Or Expired된 계정 찾기  (0) 2021.11.25
Examine IO subsystem latencies IN SQL SERVER  (0) 2021.11.24
SQL Server wait Statistics  (0) 2021.11.23
posted by bedbmsguru
2021. 11. 25. 16:45 SQL SERVER
SELECT ServerName = @@SERVERNAME
    , LoginName = sp.name
    , LockoutTime = LOGINPROPERTY(sp.name, 'LockoutTime')
    , IsLocked = LOGINPROPERTY(sp.name, 'IsLocked')
    , BadPasswordCount = LOGINPROPERTY(sp.name, 'BadPasswordCount')
    , DaysUntilExpiration = LOGINPROPERTY(sp.name, 'DaysUntilExpiration')
FROM master.sys.server_principals sp
WHERE (
       LOGINPROPERTY(sp.name, 'IsLocked') = 1
    OR LOGINPROPERTY(sp.name, 'BadPasswordCount') > 0
    OR LOGINPROPERTY(sp.name, 'DaysUntilExpiration') < 5
    )
    AND sp.type_desc = N'SQL_LOGIN'
ORDER BY sp.name;
 

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

SQL 실행횟수 모니터링  (0) 2022.03.15
DB 이관시 설정상태 체크, 변경  (0) 2022.02.07
Examine IO subsystem latencies IN SQL SERVER  (0) 2021.11.24
SQL Server wait Statistics  (0) 2021.11.23
linked server Disable하기  (0) 2021.10.21
posted by bedbmsguru
2021. 11. 23. 16:41 SQL SERVER

링크 참조

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

 

SQL Server Wait Statistics: Tell me where it hurts

Learn how to use SQL Server Wait Statistics to help diagnose performance problems.

www.sqlskills.com

 

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        -- These wait types are almost 100% never a problem and so they are
        -- filtered out to avoid them skewing the results. Click on the URL
        -- for more information.
        N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER
        N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR
        N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP
        N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH
        N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER
        N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
        N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT
        N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT
        N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT
        N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE
 
        -- Maybe comment this out if you have parallelism issues
        N'CXCONSUMER', -- https://www.sqlskills.com/help/waits/CXCONSUMER
 
        -- Maybe comment these four out if you have mirroring issues
        N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT
        N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE
        N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE
        N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD
        N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
        N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE
        N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC
        N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', -- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT
        N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX
  
       -- Maybe comment these six out if you have AG issues
        N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL
        N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
        N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT
        N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE
        N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK
        N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE
 
        N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP
        N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
        N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
        N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT
        N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE
        N'PARALLEL_REDO_DRAIN_WORKER', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_DRAIN_WORKER
        N'PARALLEL_REDO_LOG_CACHE', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_LOG_CACHE
        N'PARALLEL_REDO_TRAN_LIST', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_TRAN_LIST
        N'PARALLEL_REDO_WORKER_SYNC', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_SYNC
        N'PARALLEL_REDO_WORKER_WAIT_WORK', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_WAIT_WORK
        N'PREEMPTIVE_OS_FLUSHFILEBUFFERS', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_FLUSHFILEBUFFERS
        N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE
        N'PVS_PREALLOCATE', -- https://www.sqlskills.com/help/waits/PVS_PREALLOCATE
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
        N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE
        N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK
        N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
        N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE
        N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK
        N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH
        N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP
        N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP
        N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY
        N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY
        N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED
        N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP
        N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK
        N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK
        N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP
        N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT
        N'SOS_WORK_DISPATCHER', -- https://www.sqlskills.com/help/waits/SOS_WORK_DISPATCHER
        N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
        N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
        N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES
        N'VDI_CLIENT_OTHER', -- https://www.sqlskills.com/help/waits/VDI_CLIENT_OTHER
        N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS
        N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR
        N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN
        N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY
        N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT
        N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG
        N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE
        N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN
        N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT
        N'XE_TIMER_EVENT' -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
        )
    AND [waiting_tasks_count] > 0
    )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO
posted by bedbmsguru
2021. 10. 21. 14:36 SQL SERVER

disable 명령어가 있지는 않지만 아래처럼 data access를 막아서 사용이 불가능하도록 하는게 가능함

 

-- Disable data access

EXEC sp_serveroption @server = 'linked_server_name'

,@optname = 'data access'

,@optvalue = 'false'

-- Enable data access

EXEC sp_serveroption @server = 'linked_server_name'

,@optname = 'data access'

,@optvalue = 'true'

 

https://www.sqlservercentral.com/forums/topic/can-linked-server-be-disabled

posted by bedbmsguru
2021. 6. 23. 15:48 PostgreSQL

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

 

https://stackoverflow.com/questions/21738408/postgresql-list-and-order-tables-by-size/21738505

'PostgreSQL' 카테고리의 다른 글

DESCRIBE TABLE (  (0) 2021.06.15
pg_stat_statements  (0) 2021.06.03
blocking 체크  (0) 2020.07.24
pg_settings 칼럼 설명  (0) 2020.05.20
postgresql.conf 파일 설정  (0) 2020.04.27
posted by bedbmsguru
2021. 6. 15. 14:59 PostgreSQL

테이블 구조 확인 SQL

 

--V12.0 이상

SELECT
f.attnum AS number,
f.attname AS name,
f.attnum,
f.attnotnull AS notnull,
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
CASE
WHEN p.contype = 'p' THEN 't'
ELSE 'f'
END AS primarykey,
CASE
WHEN p.contype = 'u' THEN 't'
ELSE 'f'
END AS uniquekey,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.confkey
END AS foreignkey_fieldnum,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.conkey
END AS foreignkey_connnum,
CASE
WHEN f.atthasdef = 't' THEN pg_get_expr(d.adbin, d.adrelid)
END AS default
FROM pg_attribute f
JOIN pg_class c ON c.oid = f.attrelid
JOIN pg_type t ON t.oid = f.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
WHERE c.relkind = 'r'::char
AND n.nspname = 'schema' -- Replace with Schema name
AND c.relname = 'tablename' -- Replace with table name
AND f.attnum > 0 ORDER BY number;



--V 11이하

 

SELECT
f.attnum AS number,
f.attname AS name,
f.attnum,
f.attnotnull AS notnull,
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
CASE
WHEN p.contype = 'p' THEN 't'
ELSE 'f'
END AS primarykey,
CASE
WHEN p.contype = 'u' THEN 't'
ELSE 'f'
END AS uniquekey,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.confkey
END AS foreignkey_fieldnum,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.conkey
END AS foreignkey_connnum,
CASE
WHEN f.atthasdef = 't' THEN d.adsrc
END AS default
FROM pg_attribute f
JOIN pg_class c ON c.oid = f.attrelid
JOIN pg_type t ON t.oid = f.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
WHERE c.relkind = 'r'::char
AND n.nspname = 'schema' -- Replace with Schema name
AND c.relname = 'tablename' -- Replace with table name
AND f.attnum > 0 ORDER BY number;

 

https://stackoverflow.com/questions/109325/postgresql-describe-table

'PostgreSQL' 카테고리의 다른 글

Table Size 체크 SQL  (0) 2021.06.23
pg_stat_statements  (0) 2021.06.03
blocking 체크  (0) 2020.07.24
pg_settings 칼럼 설명  (0) 2020.05.20
postgresql.conf 파일 설정  (0) 2020.04.27
posted by bedbmsguru