Bài viết này sẽ trình bày một số phương pháp để liệt kê ra danh sách các database. Table, view, hàm, thủ tục, constraint, index… Chúng rất hữu ích khi chúng ta muốn biết thông tin về một đối tượng nào đó về database khi chúng ta viết các câu lệnh truy vấn. Mặc dù chúng có thể xem thông qua cửa sổ Object Explorer, nhưng khi chúng ta sử dụng câu lệnh truy vấn những thông tin này, chúng có thể thêm các điều kiện lọc tùy ý một cách nhanh chóng để tìm ra thức chúng ta cần tìm.
Giải pháp:
Chúng ta sẽ tìm hiểu một số thao tác làm thể sao để:
- Liệt kê danh sách database trong SQL server ở kết nối hiện tại
- Liệt kê danh sách bảng trong database.
- Liệt kê danh sách các thủ tục (Procedure)
- Liệt kê danh sách View
- Liệt kê danh sách Hàm
- Liệt kê danh sách Trigger
- Liệt kê danh sách Cột trong một bảng
- Lấy tổng số dòng trong một bảng
- Liệt kê danh sách Index
- Lấy khai báo của một View
- Liệt kê danh sách Check Constraint
- Tìm kiếm các bảng được sử dụng trong một Procedur
Liệt kê danh sách database trong SQL server ở kết nối hiện tại:
PP 1 : SP_DATABASES
PP 2 : SELECT name FROM SYS.DATABASES
PP 3 : SELECT name FROM SYS.MASTER_FILES
PP 4 : SELECT * FROM SYS.MASTER_FILES -- Type=0 for .mdf and type=1 for .ldf
Thủ tục SP_DATABASES là một thủ tục hệ thống, liệt kê danh sách database hiện có và dung lượng file của chúng.
Bảng hệ thống SYS.DATABASES sẽ liệt kê danh sách database, ngày tạo, ngày sửa, database id cùng với các thông tin khác.
SYS.MASTER_FILES sẽ liệt kê danh sách database, Id, dung lượng file, đường dẫn vật lý lưu file, và liệt kê cả file mdf và file ldf.
Liệt kê danh sách bảng
PP 1 : SELECT name FROM SYS.OBJECTS WHERE type=''U''
PP 2 : SELECT NAME FROM SYSOBJECTS WHERE xtype=''U''
PP 3 : SELECT name FROM SYS.TABLES
PP 4 : SELECT name FROM SYS.ALL_OBJECTS WHERE type=''U''
PP 5 : SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERETABLE_TYPE=''BASE TABLE''
PP 6 : SP_TABLES
Liệt kê danh sách thủ tục trong database:
PP 1 : SELECT name FROM SYS.OBJECTS WHERE type=''P''
PP 2 : SELECT name FROM SYS.PROCEDURES
PP 3 : SELECT name FROM SYS.ALL_OBJECTS WHERE type=''P''
PP 4 : SELECT NAME FROM SYSOBJECTS WHERE xtype=''P''
PP 5 : SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_TYPE=''PROCEDURE''
Bảng SYS.OBJECTS là một bảng thông dụng dùng để liệt kê tất cả thủ tục, bảng, trigger, view… Ở đây, procedure có thể lọc ra bằng cách thêm điều kiện type = ‘P’.
Information_schema.routines là view từng được sử dụng trong sql server 7.0. Còn ở phiên bản 2005 về sau, có một bảng riêng dành cho procedure (SYS.PROCEDURES ).
Liệt kê danh sách view:
PP 1 : SELECT name FROM SYS.OBJECTS WHERE type=''V''
PP 2 : SELECT name FROM SYS.ALL_OBJECTS WHERE type=''V''
PP 3 : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
PP 4 : SELECT name FROM SYS.VIEWS
Liệt kê danh sách hàm:
PP 1 : SELECT name FROM SYS.OBJECTS WHERE type=''IF'' -- inline function
PP 2 : SELECT name FROM SYS.OBJECTS WHERE type=''TF'' -- table valued function
PP 3 : SELECT name FROM SYS.OBJECTS WHERE type=''FN'' -- scalar function
PP 4 : SELECT name FROM SYS.ALL_OBJECTS WHERE type=''IF'' -- inline function
PP 5 : SELECT name FROM SYS.ALL_OBJECTS WHERE type=''TF'' -- table valued function
PP 6 : SELECT name FROM SYS.ALL_OBJECTS WHERE type=''FN'' -- scalar function
PP 7 : SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_TYPE=''FUNCTION''
Chú ý: IF - Inlined Function, TF- Table valued function, FN- Scalar Function
Liệt kê trigger trong database:
PP 1 : SELECT * FROM SYS.TRIGGERS
PP 2 : SELECT * FROM SYS.OBJECTS WHERE type=''TR''
Liệt kê trigger trong bảng
PP 1 : SP_HELPTRIGGER Products
PP 2 : SELECT * FROM SYS.TRIGGERS WHERE parent_id = object_id(''products'')
Liệt kê danh sách cột trong bảng:
PP 1 : SP_HELP Products
PP 2 : SP_COLUMNS Products
PP 3 : SELECT * FROM SYS.COLUMNS WHERE object_id = object_id(''Products'')
PP 4 : SELECT COLUMN_NAME, Ordinal_position, Data_Type, character_maximum_length FROMINFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=''Products''
Tìm một cột có trong một bảng hay không:
PP 1 : SELECT O.name FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C ON C.Object_ID= O.Object_ID WHERE C.name LIKE ''%ShipName%''
PP 2 : SELECT OBJECT_NAME(object_id) AS [Table Name] FROM SYS.COLUMNS WHEREname LIKE ''%ShipName%''
PP 3 : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERECOLUMN_NAME LIKE ''%ShipName%''
Lấy tổng số dòng trong một bảng:
PP 1 : SELECT COUNT(@@ROWCOUNT) FROM Products
PP 2 : SELECT COUNT (ProductID) FROM Products
PP 3 : SELECT OBJECT_NAME(id) AS [Table Name], rowcnt FROM SYSINDEXES
WHERE OBJECTPROPERTY(id,''isUserTable'')=1 AND indid < 2 ORDER BY rowcnt DESC
PP 4 : SELECT rowcnt FROM sysindexes WHERE id = OBJECT_ID(''Products'') AND indid < 2
PP 5 : SELECT OBJECT_NAME(OBJECT_ID) TableName,row_count FROMsys.dm_db_partition_stats WHERE object_id = object_id(''Products'') AND index_id < 2
Liệt kê danh sách Check Constraints:
PP 1 : SELECT * FROM SYS.OBJECTS WHERE type=''C''
PP 2 : SELECT * FROM sys.check_constraints
Liệt kê danh sách index trong một bảng:
PP 1 : sp_helpindex Products
PP 2 : SELECT * FROM sys.indexes WHERE object_id = object_id(''products'')
Lấy khai báo các view:
PP 1 : SELECT OBJECT_NAME(id) AS [View Name],text FROM SYSCOMMENTS WHERE id IN(SELECT object_id FROM SYS.VIEWS)
PP 2 : SELECT * FROM sys.all_sql_modules WHERE object_id IN (SELECT object_idFROMSYS.VIEWS)
PP 3 : SP_HELPTEXT ViewName
Tìm một bảng có được sử dụng trong một Procedure:
SELECT OBJECT_NAME(id) FROM SYSCOMMENTS S INNER JOIN SYS.OBJECTS O ONO.Object_Id = S.id WHERE S.text LIKE ''%Products%'' AND O.type=''P''