MSSQL SQL

MS-SQL 데이터베이스 목록 조회

SELECT
	name,
	database_id,
	create_date
FROM
	sys.databases ;

MS-SQL 테이블 용량 및 데이터 건수 확인 쿼리

SELECT
	Schema_name(tbl.schema_id) AS [SCHEMA],
	tbl.name,
	COALESCE((SELECT pr.name FROM sys.database_principals pr WITH (nolock) WHERE pr.principal_id = tbl.principal_id), Schema_name(tbl.schema_id)) AS [Owner],
	tbl.max_column_id_used AS [Columns],
	CAST(
		CASE idx.index_id WHEN 1 THEN 1
		ELSE 0
	END AS BIT) AS [HasClusIdx],
	COALESCE((SELECT SUM (spart.ROWS) FROM sys.partitions spart WITH (nolock) WHERE spart.object_id = tbl.object_id AND spart.index_id < 2), 0) AS [RowCount],
	COALESCE((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(a.used_pages - CASE WHEN a.TYPE <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.indexes AS i WITH (nolock) JOIN sys.partitions AS p WITH (nolock) ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.allocation_units AS a WITH (nolock) ON a.container_id = p.partition_id WHERE i.object_id = tbl.object_id), 0.0) / 1024 AS [IndexMB],
	COALESCE((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(CASE WHEN a.TYPE <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.indexes AS i WITH (nolock) JOIN sys.partitions AS p WITH (nolock) ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.allocation_units AS a WITH (nolock) ON a.container_id = p.partition_id WHERE i.object_id = tbl.object_id), 0.0) / 1024 AS [DataMB],
	tbl.create_date,
	tbl.modify_date
FROM sys.tables AS tbl WITH (nolock)
INNER JOIN sys.indexes AS idx WITH (nolock) ON ( idx.object_id = tbl.object_id AND idx.index_id < 2 )
INNER JOIN MASTER.dbo.spt_values v WITH (nolock) ON	( v.NUMBER = 1 AND v.TYPE = 'E' )
	--WHERE tbl.Name like '%tablename%'  
	ORDER BY 8 DESC

MS-SQL 테이블 칼럼 조회

SELECT
	a.*,
	b.rows
FROM
	(
	SELECT
		a1.*,
		a2.table_size AS siz_MB
	FROM
		INFORMATION_SCHEMA.COLUMNS AS a1
	JOIN (
		SELECT
			table_name = CONVERT(varchar(100),
			min(o.name)) ,
			table_size = CONVERT(int,
			ltrim(str(sum(reserved) * 8.192 / 1024., 15, 0))) ,
			UNIT = 'MB'
		FROM sysindexes i
		INNER JOIN sysobjects o ON (o.id = i.id)
		WHERE i.indid IN (0, 1, 255) AND o.xtype = 'U'
		GROUP BY i.id )AS a2 ON a1.TABLE_NAME = a2.TABLE_NAME ) a
JOIN (
	SELECT
		o.name,
		i.rows
	FROM sysindexes i
	INNER JOIN sysobjects o ON i.id = o.id
	WHERE i.indid < 2 AND o.xtype = 'U' ) AS b ON a.TABLE_NAME = b.name
ORDER BY table_name