Thăm dò cấu trúc và định nghĩa của bảng trong Sql Server

Thăm dò cấu trúc và định nghĩa của bảng trong Sql Server

Trong bài viết này tôi sẽ trình bày một số phương pháp thăm dò cấu trúc các bảng trong SqlServer dựa trên việc thực thi các câu truy vấn.
 

Liệt kê danh sách bảng

Thông tin danh sách các bảng do người dùng tạo ra nằm trong sys.tables:

SELECT object_id, name FROM sys.tables where name <> 'sysdiagrams' 
order by name

bảng sysdiagrams lưu thông tin dàng cho các diagram do SQL Management Studio sinh ra nên ta loại bỏ.

object_id       name
690101499       KTCL_Answer
434100587       KTCL_Campaign
786101841       KTCL_Class
722101613       KTCL_Level
1394104007      KTCL_Parent
818101955       KTCL_Part
658101385       KTCL_Question
562101043       KTCL_QuestionGroup
466100701       KTCL_School
754101727       KTCL_Skill
530100929       KTCL_Test
626101271       KTCL_TestDetail_Question
594101157       KTCL_TestDetail_UserAnswer
498100815       KTCL_User

 

Lấy về danh sách các Primary key và Unique field của các bảng

Thông tin về các ràng buộc được lưu trữ trong view INFORMATION_SCHEMA.TABLE_CONSTRAINTS. Tên các ràng buộc của bảng được lưu trữ trong view INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE:

SELECT t.TABLE_NAME, c.COLUMN_NAME, 
        case t.CONSTRAINT_TYPE 
            when 'PRIMARY KEY' then 'PK' 
            when 'UNIQUE' then 'UQ' 
        end CONSTRAINT_TYPE 
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS t, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
WHERE 
    c.Constraint_Name = t.Constraint_Name
    AND c.Table_Name = t.Table_Name
    AND (t.CONSTRAINT_TYPE = 'PRIMARY KEY' OR t.CONSTRAINT_TYPE = 'UNIQUE')
    AND t.TABLE_NAME <> 'sysdiagrams'
TABLE_NAME			COLUMN_NAME	CONSTRAINT_TYPE
KTCL_School			ID		PK
KTCL_Answer			ID		PK
KTCL_Campaign			ID		PK
KTCL_Class			ID		PK
KTCL_Level			ID		PK
KTCL_Parent			ID		PK
KTCL_Part			ID		PK
KTCL_Question			ID		PK
KTCL_QuestionGroup		ID		PK
KTCL_Skill			ID		PK
KTCL_Test			ID		PK
KTCL_TestDetail_Question	QuestionID	PK
KTCL_TestDetail_Question	TestID		PK
KTCL_TestDetail_UserAnswer	AnswerID	PK
KTCL_TestDetail_UserAnswer	TestID		PK
KTCL_User			ID		PK

 

Lấy về danh sách khóa ngoại Foreign key

Thông tin về khóa ngoại được lưu trữ trong hai bảng: sys.foreign_keys và sys.foreign_key_columns

SELECT 
	OBJECT_NAME(f.parent_object_id) AS TableName, 
	COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
	OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
	COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys f,  sys.foreign_key_columns fc 
WHERE f.OBJECT_ID = fc.constraint_object_id
TableName			ColumnName	ReferenceTableName	ReferenceColumnName 
KTCL_Level			CampaignID	KTCL_Campaign		ID
KTCL_Skill			CampaignID	KTCL_Campaign		ID
KTCL_Test			CampaignID	KTCL_Campaign		ID
KTCL_Class			SchoolID	KTCL_School		ID
KTCL_Test			UserID		KTCL_User		ID
KTCL_Parent			ChildID		KTCL_User		ID
KTCL_TestDetail_Question	TestID		KTCL_Test		ID
KTCL_TestDetail_UserAnswer	TestID		KTCL_Test		ID
KTCL_Question			GroupID		KTCL_QuestionGroup	ID
KTCL_Answer			QuestionID	KTCL_Question		ID
KTCL_Part			SkillID		KTCL_Skill		ID
KTCL_User			ClassID		KTCL_Class		ID
KTCL_QuestionGroup		PartID		KTCL_Part		ID

 

Lấy về danh sách và thông tin các cột trong bảng

Lấy về thông tin về tên, kiểu dữ liệu, độ dài dữ liệu, có được null hay không, có phải là identity hay không, có phải là computed hay không.
Các thông tin trên được lưu trữ trong các bảng: sys.columns, sys.tables và sys.types
Ở đây tôi sẽ lấy ví dụ với bảng KTCL_Question, với các bảng khác bạn chỉ việc thay tên

select c.name, t.name datatype, c.max_length, 
		c.is_nullable, c.is_identity, c.is_computed 
from sys.columns c, sys.tables tbl, sys.types t 
where c.object_id = tbl.object_id 
		and c.user_type_id=t.user_type_id 
		and tbl.name='KTCL_Question' 
order by column_id
name		datatype	max_length	is_nullable	is_identity	is_computed
ID		int		4		0		1		0
GroupID		int		4		0		0		0
Data		nvarchar	-1		1		0		0
RandomMode	int		4		1		0		0
MaxShownAnswers	int		4		1		0		0
Visible		bit		1		1		0		0
AnswerColumn	int		4		1		0		0
AnswerPosition	int		4		1		0		0
Title		nvarchar	-1		1		0		0

với các kiểu varchar, nvarchar, varbinary: nếu max_length=-1 thì tương ứng là max
 

Lấy thông tin giá trị default của cột

Thông tin về giá trị mặc định được lưu trữ trong bảng sys.columns, ta sử dụng hàm object_definition để lấy về giá trị:

SELECT name, object_definition(default_object_id) value 
FROM sys.columns 
WHERE  default_object_id > 0 
	and object_id = object_id('KTCL_Question')
name		value
RandomMode	((0))
MaxShownAnswers	((4))
Visible		((1))
AnswerColumn	((2))
AnswerPosition	((1))

 

Lấy về danh sách các Index

Thông tin về các index được lưu trữ trong bảng sys.indexes:

SELECT 
	t.name TableName, col.name ColumnName, 
	ind.name IndexName, ind.type, ind.is_unique 
FROM sys.indexes ind 
INNER JOIN sys.index_columns ic 
	ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN sys.columns col 
	ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN sys.tables t 
	ON ind.object_id = t.object_id 
WHERE ind.is_primary_key = 0 
	AND ind.is_unique_constraint = 0 
	AND ind.is_disabled = 0 
	AND ind.is_hypothetical = 0 
ORDER BY 
	TableName, ic.key_ordinal
TableName	ColumnName	IndexName			type	is_unique
Attachment	McsAttachmentId	IDX_Attachment_McsAttachmentId	2	1
Email		TrackingId	IDX_Email_TrackingId		2	1

 

Lấy về danh sách các Check-Constraints

Thông tin về các Check-Constraints được lưu trữ trong view INFORMATION_SCHEMA.CHECK_CONSTRAINTS:

SELECT TABLE_NAME, COLUMN_NAME,
	cc.CONSTRAINT_NAME, CHECK_CLAUSE
FROM  INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
	ON cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
ORDER BY TABLE_NAME, COLUMN_NAME
TABLE_NAME	COLUMN_NAME	CONSTRAINT_NAME		CHECK_CLAUSE
Email		DeliveredTime	CHK_Email_DeliveredTime	([DeliveredTime]>[ReceivedTime])
Email		ReceivedTime	CHK_Email_DeliveredTime	([DeliveredTime]>[ReceivedTime])

 

Lấy về Description (comment) của bảng

Sử dụng hàm fn_listextendedproperty để lấy về thông tin comment:

SELECT objname, value 
FROM fn_listextendedproperty('MS_DESCRIPTION','schema', 'dbo', 'table', null, null, null)
objname		value 
KTCL_Question	Bảng chứa các câu hỏi

 

Lấy về Description (comment) của cột

Ta cũng sử dụng hàm fn_listextendedproperty để lấy về thông tin comment, thêm thông tin tên bảng:

SELECT objname, value 
FROM fn_listextendedproperty('MS_DESCRIPTION','schema', 'dbo', 'table', 'KTCL_Question', 'column', null)
objname		value 
GroupID		Mã nhóm
Data		Dữ liệu dạng HTML ở dưới tiêu đề
RandomMode	0: Randomize all answers  1: Randomize all answers except the last  2: Don't randomize any answers
MaxShownAnswers	x: show maximum x answer(s)    0: show all answers (unlimited)
AnswerColumn	0: Horizontal (1 row)   1: Vertical (1 column)   x: x columns
AnswerPosition	1: input bên trái   2: input bên phải   3: input bên trên   4: input bên dưới
Title		Tiêu đề của câu hỏi

 

Lấy về số bản ghi của bảng

Thông tin về số bản ghi được lưu trữ trong bảng sys.partitions:

select tbl.name, TableRowCount = (
             select sum (spart.rows) from sys.partitions spart
             where spart.object_id = tbl.object_id and spart.index_id < 2 )
from sys.tables tbl
where tbl.name <> 'sysdiagrams'
name				TableRowCount
KTCL_Campaign			1
KTCL_School			1
KTCL_User			773
KTCL_Test			1
KTCL_QuestionGroup		6
KTCL_TestDetail_UserAnswer	30
KTCL_TestDetail_Question	30
KTCL_Question			30
KTCL_Answer			110
KTCL_Level			7
KTCL_Skill			2
KTCL_Class			18
KTCL_Part			6
KTCL_Parent			0

 

Khi trích dẫn bài viết từ tek.eten.vn, xin vui lòng ghi rõ nguồn. Chúng tôi sẽ rất cảm ơn bạn!