Sqlserver中的一些技巧

获取一个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);
??? }

?