服務(wù)器上的數(shù)據(jù)庫(kù)

(1)方法一

select name 數(shù)據(jù)庫(kù)名，

database_id 數(shù)據(jù)庫(kù)ID

from sys.databases

(2)方法二

Exec sp_helpdb

數(shù)據(jù)庫(kù)文件

(1)方法一 跟當(dāng)前數(shù)據(jù)庫(kù)有關(guān)

select type_desc,

name,

physical_name,

size,

max_size,

growth

from sys.database_files

(2)方法二

select name,

physical_name

from sys.master_files

where database_id=db_id('mydatabase')

數(shù)據(jù)庫(kù)的數(shù)據(jù)表

use mydatabase --跟當(dāng)前數(shù)據(jù)庫(kù)有關(guān)

go

select *

from information_schema.tables

表結(jié)構(gòu)及相關(guān)信息

use mydatabase --跟當(dāng)前數(shù)據(jù)庫(kù)有關(guān)

go

select column_name 列名，

data_type 數(shù)據(jù)類(lèi)型，

isnull(character_maximum_length,'')長(zhǎng)度，

isnull(column_default,'')默認(rèn)值

from information_schema.columns

where table_name='student'

n 獲取指定數(shù)據(jù)表的字段數(shù)

n use mydatabase --跟當(dāng)前數(shù)據(jù)庫(kù)有關(guān)

DECLARE @FieldNum int;

SET @FieldNum = (SELECT COUNT(*)

FROM information_schema.columns

WHERE TABLE_NAME='student');

PRINT N'表student中字段的個(gè)數(shù)：'+CAST(@FieldNum AS varchar(10));

go

-- 查看當(dāng)前數(shù)據(jù)庫(kù)所喲表信息

use mydatabase --跟當(dāng)前數(shù)據(jù)庫(kù)有關(guān)

SELECT DISTINCT table_name

FROM information_schema.columns

數(shù)據(jù)庫(kù)中的視圖

use mydatabase --跟當(dāng)前數(shù)據(jù)庫(kù)有關(guān)

go

select *

from information_schema.views

數(shù)據(jù)庫(kù)中的架構(gòu)

use mydatabase --跟當(dāng)前數(shù)據(jù)庫(kù)有關(guān)

go

select

schema_name 架構(gòu)名，

schema_owner 擁有者

from information_schema.schemata

更多信息請(qǐng)查看IT技術(shù)專(zhuān)欄