2016年2月3日 星期三

[研究] 用SQL指令找出資料庫的資料表、欄位名、PK欄位、資料筆數

[研究] 用SQL指令找出資料庫的資料表、欄位名、PK欄位

2016-02-03
2016-02-15 最新更新

Windows Azure SQL Database  (SQL Server 12.0.2000.8) 上

列出所有資料表(table) 名稱

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME

select * from information_schema.tables


列出所有資料表(table) 的筆數

select o.name, i.rows from sysobjects o inner join sysindexes i on o.id=i.id where i.indid = 1 order by i.rows desc


列出所有資料表(table) 的筆數 (僅限使用者資料表,不含系統資料表)

select o.name, i.rows from sysobjects o inner join sysindexes i on o.id=i.id where i.indid = 1 and xtype='U'  order by i.rows desc



列出資料庫中所有欄位(field)資訊

select * from information_schema.columns

select * from sys.columns


列出某資料表中所有欄位(field)資訊

SELECT COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '資料表名稱'


列出資料表中PK欄位

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '資料表名稱'



列出所有資料表大小,參考
http://www.sqldocumentor.com/table-size-in-sql-server-find-rows-and-disk-space-usage


SELECT t.schema_name+ ' – '+ t.table_name as schema_table
, t.index_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
, sum(t.tbl_rows) as rows
from
(
SELECT s.Name schema_name
, o.Name table_name
, coalesce(i.Name, 'HEAP') index_name
, p.used_page_count * 8 used
, p.reserved_page_count * 8 reserved
, p.row_count ind_rows
, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end tbl_rows
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o
ON o.object_id = p.object_id
INNER JOIN sys.schemas as s
ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes as i
on i.object_id = p.object_id and i.index_id = p.index_id
WHERE o.type_desc = 'USER_TABLE'
and o.is_ms_shipped = 0
) as t
GROUP BY
t.schema_name, t.table_name
, t.index_name
ORDER BY
5 desc



資料庫改名 (實測在 Windows Azure SQL Database 上可用)

sp_rename [_TestTable],[_TestTable2]

注意
Caution: Changing any part of an object name could break scripts and stored procedures.

(完)

資訊結構描述檢視 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms186778(v=sql.120).aspx

沒有留言:

張貼留言