您现在正在浏览：站长之家 >> 数据库 >> 详细内容

Sqlserver中的一些技巧

时间:2007-12-23 来源:不详作者:迈克DB

获取一个SQLServer上的所有数据库信息用存储过程sp_databases
在查询分析其中执行命令execsp_databases
结果:master14464NULL
model1280NULL
msdb14336NULL
Northwind4352NULL
pubs2560NULL
Store1912NULL
tempdb8704NULL
test1272NULL
获取一个数据库的所有表用存储过程sp_tables
执行命令:useNorthwindexecsp_tables结果:
NorthwinddbosysusersSYSTEMTABLENULL
NorthwinddboCategoriesTABLENULL
NorthwinddboCustomerCustomerDemoTABLENULL
NorthwinddboCustomerDemographicsTABLENULL
NorthwinddboCustomersTABLENULL
NorthwinddbodtpropertiesTABLENULL
NorthwinddboEmployeesTABLENULL
NorthwinddboEmployeeTerritoriesTABLENULL
(.......)

获取一个表的列信息用存储过程sp_columns
运行execsp_columns’Orders’(Orders为表名)结果
NorthwinddboOrdersOrderID4intidentity1040100NULLNULL4NULLNULL1NO56
NorthwinddboOrdersCustomerID-8nchar510NULLNULL1NULLNULL-8NULL102YES39
NorthwinddboOrdersEmployeeID4int1040101NULLNULL4NULLNULL3YES38
NorthwinddboOrdersOrderDate11datetime23163NULL1NULLNULL93NULL4YES111 copyright dedecms
NorthwinddboOrdersRequiredDate11datetime23163NULL1NULLNULL93NULL5YES111
NorthwinddboOrdersShippedDate11datetime23163NULL1NULLNULL93NULL6YES111
NorthwinddboOrdersShipVia4int1040101NULLNULL4NULLNULL7YES38
NorthwinddboOrdersFreight3money19214101NULL(0)3NULLNULL8YES110
NorthwinddboOrdersShipName-9nvarchar4080NULLNULL1NULLNULL-9NULL809YES39
(......)
获取一个数据库的所有存储过程,可以用
select*fromsysobjectswheretype=’p’
执行所得结果:
CustOrdersDetail789577851P1016106127360002000-08-0601:34:52.513
CustOrdersOrders805577908P1016106127360002000-08-0601:34:52.733
CustOrderHist821577965P1016106127360002000-08-0601:34:52.967
SalesByCategory837578022P1016106127360002000-08-0601:34:53.200
(......)
sysobjects这个东西还有其他一些用法,具体可参照SQLServer连机帮助
在ADO.NET里面获取一个存储过程的参数信息:
SqlConnectionconnect=newSqlConnection(ConnectionString);
connect.Open();
SqlCommandsc=newSqlCommand("SalesByCategory",connect);//SalesByCategory为Northwind数据库中的一个存储过程. 内容来自dedecms
sc.CommandType=CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(sc);
foreach(SqlParameterparaminsc.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);
}