Some skills in SQL Server

xiaoxiao2021-03-06  114

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;

???}

?

转载请注明原文地址:https://www.9cbs.com/read-126904.html

New Post(0)