获取一个SQLServer上的所有数据库信息 用存储过程 sp_databases
在查询分析其中执行命令exec sp_databases
结果:master
14464 NULLmodel
1280 NULLmsdb
14336 NULLNorthwind
4352 NULLpubs
2560 NULLStore
1912 NULLtempdb
8704 NULLtest
1272 NULL获取一个数据库的所有表用存储过程 sp_tables
执行命令:
use Northwind
exec sp_tables 结果:
Northwind dbo sysusers SYSTEM
TABLE NULLNorthwind dbo Categories
TABLE NULLNorthwind dbo CustomerCustomerDemo
TABLE NULLNorthwind dbo CustomerDemographics
TABLE NULLNorthwind dbo Customers
TABLE NULLNorthwind dbo dtproperties
TABLE NULLNorthwind dbo Employees
TABLE NULLNorthwind 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
56Northwind dbo Orders CustomerID
-8 nchar 5 10 NULL NULL 1 NULL NULL -8 NULL 10 2 YES
39Northwind dbo Orders EmployeeID
4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 3 YES
38Northwind dbo Orders OrderDate
11 datetime 23 16 3 NULL 1 NULL NULL 9 3 NULL 4 YES
111Northwind dbo Orders RequiredDate
11 datetime 23 16 3 NULL 1 NULL NULL 9 3 NULL 5 YES
111Northwind dbo Orders ShippedDate
11 datetime 23 16 3 NULL 1 NULL NULL 9 3 NULL 6 YES
111Northwind dbo Orders ShipVia
4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 7 YES
38Northwind dbo Orders Freight
3 money 19 21 4 10 1 NULL (
0)
3 NULL NULL 8 YES
110Northwind 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.513CustOrdersOrders
805577908 P
1 0 1610612736 0 0 0 2000-08-06 01:
34:
52.733CustOrderHist
821577965 P
1 0 1610612736 0 0 0 2000-08-06 01:
34:
52.967SalesByCategory
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,如需转载请自行联系原作者