SP_Help :
This function (Store Procedure) you can use to know the User Tables, System Table, View, Trigger, Stored Procedure and etc in selected database.
- If SP_Help is executed without parameter it will give the output User Tables, System Table, View, Trigger, Stored Procedure and etc in selected database.
- If SP_Help is provided Table Name as Parameter it will give all the details of the table such as Column_Name, Column_Type i.e. Data Type, Indexes of the Table.
Another way to see the Column Names of the Table is as follows
SELECT * FROM 'TableName' where 1=2
_________________________________________________________________________
System Function
@@Identity
Return Type
Numeric(38,0)
Few points of the @@Identity function
- This system function returns the Last Identity value that is generated by an INSERT, SELECT INTO, or bulk copy statement is completed.
- If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
- If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
- If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers.
- If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
- Failed statements and transactions can change the current identity for a table and create gaps in the identity column values.
- The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed.
- For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.
- @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
_________________________________________________________________________
XP_MSVer :
Is SQL Server Store Procedure which return information about the actual build number of the server and information about the SQL Server. The information that xp_msver returns can be used within Transact-SQL statements, batches, stored procedures, and so on, to enhance logic for platform-independent code.
Output of the execution is as follows
Index Name Internal_Value Character_Value
-------------------------------------------------------------------------------------
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 655360 10.0.2531.0
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL SQL
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2007.0100.2531.00 ((Katmai_PCU_Main).090329-1015 )
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft SQL Server is a registered trademark of Microsoft Corporation.
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 165871616 NULL
15 WindowsVersion 393347078 6.0 (6002)
16 ProcessorCount 2 2
17 ProcessorActiveMask 3 00000003
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 3542 3542 (3714453504)
20 Product ID NULL NULL
-------------------------------------------------------------------------------------
_________________________________________________________________________
SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGO--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOANSI NULL ON/OFF:
This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.
QUOTED IDENTIFIER ON/OFF:
This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.
This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.
_________________________________________________________________________
