Some tips in SQL Server

server|sqlserver| Tips
To obtain all database information on a SQL Server using stored procedures sp_databases

Execute command in query analysis exec sp_databases

Result: Master 14464 NULL
Model 1280 NULL
msdb 14336 NULL
Northwind 4352 NULL
Pubs 2560 NULL
Store 1912 NULL
tempdb 8704 NULL
Test 1272 NULL

Gets a stored procedure for all tables in a database sp_tables

Execute command: Use Northwind exec sp_tables results:

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
(.......)


Gets the column information for a table with stored procedures Sp_columns

Run exec sp_columns ' orders ' (Orders for table names) results

Northwind dbo Orders OrderID 4 int identity 4 0 0 NULL NULL 4 NULL NULL 1 NO 56
Northwind dbo Orders CustomerID-8 nchar 5 NULL NULL 1 NULL NULL-8 NULL 2 YES 39
Northwind dbo Orders EmployeeID 4 int 4 0 1 NULL NULL 4 NULL NULL 3 YES 38
Northwind dbo Orders OrderDate 3 null 1 NULL 9 3 NULL 4 YES 111
Northwind dbo Orders requireddate 3 null 1 NULL 9 3 null 5 YES 111
Northwind dbo Orders shippeddate 3 null 1 NULL 9 3 null 6 YES 111
Northwind dbo Orders ShipVia 4 int 4 0 1 NULL NULL 4 NULL NULL 7 YES 38
Northwind dbo Orders Freight 3 $4 1 null (0) 3 NULL NULL 8 YES 110
Northwind dbo Orders ShipName-9 nvarchar null null 1 null NULL-9 NULL 9 YES 39
(......)

Gets all the stored procedures for a database, which you can use

SELECT * from sysobjects where type= ' P '

Results of implementation:

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 this thing there are other uses, specific reference to SQL Server computer Help

In Ado.net, get the parameter information for a stored procedure:
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);
}