SQL Server數(shù)據(jù)庫(kù)中常用的sql語(yǔ)句有哪些？
此文主要向大家講述的是SQL Server數(shù)據(jù)庫(kù)中的一些經(jīng)常使用的sql語(yǔ)句的描述，在此篇文章里，我列舉一些常用的sql語(yǔ)句來(lái)對(duì)數(shù)據(jù)庫(kù)，數(shù)據(jù)表，視圖等等進(jìn)行介紹。當(dāng)我們?cè)谑褂貌樵儾樵儾僮鲿r(shí)這些sql語(yǔ)句都是非常有用的。
雖然在SQL Server對(duì)象瀏覽器中我們也可以獲得這些語(yǔ)句，但是如果我們寫這些語(yǔ)句時(shí)我們可以將它
簡(jiǎn)介
在這篇文章中，我列舉一些sql語(yǔ)句來(lái)介紹數(shù)據(jù)庫(kù)，數(shù)據(jù)表，視圖等等。當(dāng)我們?cè)谑褂貌樵儾樵儾僮鲿r(shí)這些sql語(yǔ)句都是非常有用的。雖然在SQL Server數(shù)據(jù)庫(kù)對(duì)象瀏覽器中我們也可以獲得這些語(yǔ)句，但是如果我們寫這些語(yǔ)句時(shí)我們可以將它自定義。這就意味著我們可以給予自己的需求來(lái)過(guò)濾結(jié)果。
sql語(yǔ)句列表
如何列舉SQL Server數(shù)據(jù)庫(kù)當(dāng)前連接的可用數(shù)據(jù)庫(kù)
- Method 1 : SP_DATABASES
- Method 2 : SELECT name FROM SYS.DATABASES
- Method 3 : SELECT name FROM SYS.MASTER_FILES
- Method 4 : SELECT * FROM SYS.MASTER_FILES -- Type=0 for .mdf and type=1 for .ldf
SP_DATABASES是一個(gè)可以列舉數(shù)據(jù)庫(kù)及其大小的存儲(chǔ)過(guò)程
sys.databases語(yǔ)句中可以列舉數(shù)據(jù)庫(kù)名稱，創(chuàng)建日期，修改日期，已經(jīng)數(shù)據(jù)庫(kù)id和其他一些信息。
SYS.MASTER_FILES語(yǔ)句可以查詢數(shù)據(jù)的詳細(xì)情況，比如數(shù)據(jù)庫(kù)id，大小，物理存儲(chǔ)路徑以及列舉數(shù)據(jù)庫(kù)mdf和ldf.
如何列舉數(shù)據(jù)庫(kù)中的數(shù)據(jù)表
以下的sql語(yǔ)句都可以列表SQL Server數(shù)據(jù)庫(kù)中的用戶表.
- Method 1 : SELECT name FROM SYS.OBJECTS WHERE type='U'
- Method 2 : SELECT NAME FROM SYSOBJECTS WHERE xtype='U'
- Method 3 : SELECT name FROM SYS.TABLES
- Method 4 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='U'
- Method 5 : SELECT table_name FROM INFORMATION_SCHEMA.
- TABLES WHERE TABLE_TYPE='BASE TABLE'
- Method 6 : SP_TABLES
如何列舉數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程
- Method 1 : SELECT name FROM SYS.OBJECTS WHERE type='P'
- Method 2 : SELECT name FROM SYS.PROCEDURES
- Method 3 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='P'
- Method 4 : SELECT NAME FROM SYSOBJECTS WHERE xtype='P'
- Method 5 : SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'
SYS.OBJECTS數(shù)據(jù)表包含了全部的存儲(chǔ)過(guò)程，數(shù)據(jù)表，觸發(fā)器，視圖等的信息，這里使用type=’p'來(lái)查詢存儲(chǔ)過(guò)程.
Information_schema.routines在SQL Server 7.0是一個(gè)數(shù)據(jù)視圖，在其后的版本中已經(jīng)變成存儲(chǔ)過(guò)程專有的表.
如何列舉數(shù)據(jù)庫(kù)中的視圖
- Method 1 : SELECT name FROM SYS.OBJECTS WHERE type='V'
- Method 2 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='V'
- Method 3 : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
- Method 4 : SELECT name FROM SYS.VIEWS
如何列舉數(shù)據(jù)庫(kù)中的函數(shù)
- Method 1 : SELECT name FROM SYS.OBJECTS WHERE type='IF' -- inline function
- Method 2 : SELECT name FROM SYS.OBJECTS WHERE type='TF' -- table valued function
- Method 3 : SELECT name FROM SYS.OBJECTS WHERE type='FN' -- scalar function
- Method 4 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='IF' -- inline function
- Method 5 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='TF' -- table valued function
- Method 6 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='FN' -- scalar function
- Method 7 : SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'
如何列舉數(shù)據(jù)庫(kù)中的觸發(fā)器
- Method 1 : SP_HELPTRIGGER Products
- Method 2 : SELECT * FROM SYS.TRIGGERS WHERE parent_id = object_id('products')
下面我以一個(gè)products表為例列舉一些對(duì)表的操作.
如何獲取數(shù)據(jù)表中的列
- Method 1 : SP_HELP Products
- Method 2 : SP_COLUMNS Products
- Method 3 : SELECT * FROM SYS.COLUMNS WHERE object_idobject_id = object_id('Products')
- Method 4 : SELECT COLUMN_NAME,Ordinal_position,Data_Type,
- character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Products'
如何根據(jù)數(shù)據(jù)表的列查找數(shù)據(jù)表的名稱
- Method 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%'
- Method 2 : SELECT OBJECT_NAME(object_id) AS [Table Name] FROM SYS.COLUMNS WHERE name LIKE '%ShipName%'
- Method 3 : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%ShipName%'
如何獲得數(shù)據(jù)表的總列數(shù)
- Method 1 : SELECT COUNT(@@ROWCOUNT) FROM Products
- Method 2 : SELECT COUNT (ProductID) FROM Products
- Method 3 : SELECT OBJECT_NAME(id) AS [Table Name],rowcnt FROM SYSINDEXES
- WHERE OBJECTPROPERTY(id,'isUserTable')=1 AND indid < 2 ORDER BY rowcnt DESC
- Method 4 : SELECT rowcnt FROM sysindexes WHERE id = OBJECT_ID('Products') AND indid < 2
- Method 5 : SELECT OBJECT_NAME(OBJECT_ID) TableName,
- row_count FROM sys.dm_db_partition_stats WHERE object_idobject_id = object_id('Products') AND index_id < 2
如何獲得數(shù)據(jù)表的約束
- Method 1 : SELECT * FROM SYS.OBJECTS WHERE type='C'
- Method 2 : SELECT * FROM sys.check_constraints
如何獲得數(shù)據(jù)表的索引
- Method 1 : sp_helpindex Products
- Method 2 : SELECT * FROM sys.indexes WHERE object_idobject_id = object_id('products')
如何獲得數(shù)據(jù)視圖的模式定義
- Method 1 : SELECT OBJECT_NAME(id) AS [View Name],text FROM SYSCOMMENTS WHERE id IN (SELECT object_id FROM SYS.VIEWS)
- Method 2 : SELECT * FROM sys.all_sql_modules WHERE object_id IN (SELECT object_id FROM SYS.VIEWS)
- Method 3 : SP_HELPTEXT ViewName
如何獲得存儲(chǔ)過(guò)程中的數(shù)據(jù)表
- Method 1 : SELECT OBJECT_NAME(id) FROM SYSCOMMENTS S
- INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id
- WHERE S.text LIKE '%Products%'
- AND O.type='P'
總結(jié)
以上列舉了一下SQL Server數(shù)據(jù)庫(kù)用實(shí)用的一些sql語(yǔ)句，希望對(duì)你在使用查詢窗口操作時(shí)有用。
【編輯推薦】
- 巧妙SQL Server 文件大小的步驟
- SQL Server2000連接錯(cuò)誤的緣由有哪些？
- SQL Server實(shí)例中對(duì)另個(gè)實(shí)例的調(diào)用
- SQL Server 2000的安全策略的正確打造
- SQL Server 數(shù)據(jù)導(dǎo)入的實(shí)際行為規(guī)范描述
