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

?