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.
顯示所有欄位定義
SELECT
INFORMATION_SCHEMA.TABLES.TABLE_NAME as 資料表名稱,
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME as 欄位名稱,
INFORMATION_SCHEMA.COLUMNS.DATA_TYPE as 欄位資料類型,
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH as 欄位最大長度,
INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT as 欄位預設值,
INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE as 欄位允許空值,
(
SELECT
value
FROM
fn_listextendedproperty (NULL, 'schema', 'dbo', 'table',
INFORMATION_SCHEMA.TABLES.TABLE_NAME, 'column', default)
WHERE
name='MS_Description'
and objtype='COLUMN'
and objname Collate Chinese_Taiwan_Stroke_CI_AS=INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
) as 欄位描述
FROM
INFORMATION_SCHEMA.TABLES
LEFT JOIN INFORMATION_SCHEMA.COLUMNS ON (INFORMATION_SCHEMA.TABLES.TABLE_NAME=INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
WHERE
TABLE_TYPE='BASE TABLE'
ORDER BY
INFORMATION_SCHEMA.TABLES.TABLE_NAME, ordinal_position
|
顯示所有欄位定義 (另一方法)
SELECT sysobjects.name as '資料表名稱',
syscolumns.colid '欄位編號',
syscolumns.name '欄位名稱',
systypes.name '欄位型態',
syscolumns.prec '欄位精確度',
syscolumns.scale '欄位規模',
syscolumns.length '欄位長度',
syscomments.TEXT '欄位預設值',
CASE
WHEN syscolumns.isnullable = 1 THEN 'Y'
ELSE 'N'
END '欄位允許NULL',
CASE
WHEN syscolumns.status & 0X80 = 0X80 THEN 'Y'
ELSE 'N'
END '欄位識別規格(is_identity)',
(SELECT VALUE
FROM Fn_listextendedproperty (NULL, 'schema', 'dbo', 'table',
sysobjects.name,
'column',
syscolumns.name)) '欄位描述'
FROM sysobjects ,
syscolumns
LEFT OUTER JOIN syscomments
INNER JOIN sysobjects obj
ON syscomments.id = obj.id
ON syscolumns.cdefault = syscomments.id
AND syscomments.colid = 1,
systypes
WHERE sysobjects.id = syscolumns.id
AND sysobjects.xtype = 'U'
AND syscolumns.xusertype = systypes.xusertype
ORDER BY
sysobjects.name, syscolumns.colid
|
資訊結構描述檢視 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms186778(v=sql.120).aspx
沒有留言:
張貼留言