Get all database information storage procedures SP_DATABASES on a SQLServer
Introduction to the query, execute command exec sp_databases
RESULTS: Master? 14464? NullModel? 1280? Nullmsdb? 14336? Nullnorthwind? 4352? Nullpubs? 2560? NullStore? 1912? NullTempdb? 8704? NullTest? 1272? Null
Get all table stored procedures sp_tables for a database
Execute command: use northwind exec sp_tables results:
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 (.......)
Get a list of information stored procedures SP_COLUMNS
Run EXEC SP_COLUMNS 'ORDERS' (ORDERS as a table name)
Northwind? DBO? ORDERS? Orderid? 4? Int Identity? 10? 4? 0? 10? 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? EMPLOYEID? 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? 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 (...)
Get all stored procedures for a database, you can use
Select * from sysobjects where type = 'p "SERE TYPE =' P '
Execute the result:
CustOrDersdetail? 789577851? P? 1? 0? 0? 0? 2000-08-06 01: 34: 52.513CustRDERSORDERS? 805577908? P? 1? 0? 1610612736? 0? 0? 0? 2000-08- 06 01: 34: 52.733CustOrhist? 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 There are other usages, specific to SQL Server connectivity
Get a parameter information for a stored procedure in ADO.NET: ???? SqlConnection Connect = New SqlConnection (Connectionstring);
???? connect.open ();
???? sqlcommand sc = new sqlcommand ("Salesbycategory", Connect); // SalesbyCategory is a stored procedure in the Northwind database.
???? 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;
???}
?