SQL Server的一些技巧

擷取一個SQLServer上的所有資料庫資訊 用預存程序 sp_databases

在查詢分析其中執行命令exec sp_databases

結果:master 14464 NULL

model 1280 NULL

msdb 14336 NULL

Northwind 4352 NULL

pubs 2560 NULL

Store 1912 NULL

tempdb 8704 NULL

test 1272 NULL

擷取一個資料庫的所有表用預存程序 sp_tables

執行命令: use Northwind exec sp_tables 結果:

Northwind dbo sysusers SYSTEM TABLE NULL

Northwind dbo Categories TABLE NULL

Northwind dbo CustomerCustomerDemo TABLE NULL

Northwind dbo CustomerDemographics TABLE NULL

Northwind dbo Customers TABLE NULL

Northwind dbo dtproperties TABLE NULL

Northwind dbo Employees TABLE NULL

Northwind dbo EmployeeTerritories TABLE NULL

(.......)

擷取一個表的列資訊用預存程序sp_columns

運行exec sp_columns 'Orders' (Orders為表名) 結果

Northwind dbo Orders OrderID 4 int identity 10 4 0 10 0 NULL NULL 4 NULL NULL 1 NO 56

Northwind dbo Orders CustomerID -8 nchar 5 10 NULL NULL 1 NULL NULL -8 NULL 10 2 YES 39

Northwind dbo Orders EmployeeID 4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 3 YES 38

Northwind dbo Orders OrderDate 11 datetime 23 16 3 NULL 1 NULL NULL 9 3 NULL 4 YES 111

Northwind dbo Orders RequiredDate 11 datetime 23 16 3 NULL 1 NULL NULL 9 3 NULL 5 YES 111

Northwind dbo Orders ShippedDate 11 datetime 23 16 3 NULL 1 NULL NULL 9 3 NULL 6 YES 111

Northwind dbo Orders ShipVia 4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 7 YES 38

Northwind dbo Orders Freight 3 money 19 21 4 10 1 NULL (0) 3 NULL NULL 8 YES 110

Northwind dbo Orders ShipName -9 nvarchar 40 80 NULL NULL 1 NULL NULL -9 NULL 80 9 YES 39

(......)

擷取一個資料庫的所有預存程序,可以用

select * from sysobjects where type='p'

執行所得結果:

CustOrdersDetail 789577851 P 1 0 1610612736 0 0 0 2000-08-06 01:34:52.513

CustOrdersOrders 805577908 P 1 0 1610612736 0 0 0 2000-08-06 01:34:52.733

CustOrderHist 821577965 P 1 0 1610612736 0 0 0 2000-08-06 01:34:52.967

SalesByCategory 837578022 P 1 0 1610612736 0 0 0 2000-08-06 01:34:53.200

(......)

sysobjects這個東西還有其他一些用法,具體可參照SQLServer連機協助

在ADO.NET裡面擷取一個預存程序的參數資訊:

SqlConnection connect = new SqlConnection(ConnectionString);

connect.Open();

SqlCommand sc = new SqlCommand("SalesByCategory", connect); // SalesByCategory 為Northwind資料庫中的一個預存程序.

sc.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(sc);

foreach(SqlParameter param in sc.Parameters)

{

Console.WriteLine("Name:{0}, Size:{1}, Type:{2}, Value:{3},Direction:{4}, IsNull:{5}", param.ParameterName, param.Size, param.DbType, param.Value, param.Direction, param.IsNullable);

}