服務(wù)器上的數(shù)據(jù)庫
(1)方法一
select name 數(shù)據(jù)庫名，
database_id 數(shù)據(jù)庫ID
from sys.databases
(2)方法二
Exec sp_helpdb
數(shù)據(jù)庫文件
(1)方法一 跟當(dāng)前數(shù)據(jù)庫有關(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ù)庫的數(shù)據(jù)表
use mydatabase --跟當(dāng)前數(shù)據(jù)庫有關(guān)
go
select *
from information_schema.tables
表結(jié)構(gòu)及相關(guān)信息
use mydatabase --跟當(dāng)前數(shù)據(jù)庫有關(guān)
go
select column_name 列名，
data_type 數(shù)據(jù)類型，
isnull(character_maximum_length,'')長度，
isnull(column_default,'')默認(rèn)值
from information_schema.columns
where table_name='student'
n 獲取指定數(shù)據(jù)表的字段數(shù)
n use mydatabase --跟當(dāng)前數(shù)據(jù)庫有關(guān)
DECLARE @FieldNum int;
SET @FieldNum = (SELECT COUNT(*)
FROM information_schema.columns
WHERE TABLE_NAME='student');
PRINT N'表student中字段的個數(shù)：'+CAST(@FieldNum AS varchar(10));
go
-- 查看當(dāng)前數(shù)據(jù)庫所喲表信息
use mydatabase --跟當(dāng)前數(shù)據(jù)庫有關(guān)
SELECT DISTINCT table_name
FROM information_schema.columns
數(shù)據(jù)庫中的視圖
use mydatabase --跟當(dāng)前數(shù)據(jù)庫有關(guān)
go
select *
from information_schema.views
數(shù)據(jù)庫中的架構(gòu)
use mydatabase --跟當(dāng)前數(shù)據(jù)庫有關(guān)
go
select
schema_name 架構(gòu)名，
schema_owner 擁有者
from information_schema.schemata