擷取一個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);
}
本文轉自高海東部落格園部落格，原文連結：http://www.cnblogs.com/ghd258/archive/2005/10/24/260745.html，如需轉載請自行聯系原作者