MSSQL Tutorial System Settings 101 codes

1 @@CONNECTIONS Returns the number of connections made to the SQL Server instance since it was last started

2 @@CPU_BUSY Shows the number of busy CPU milliseconds since the SQL Server instance was last started

3 @@error is

4 @@ERROR reset

5 @@IDLE Displays the total idle time of the SQL Server instance in milliseconds, since the instance was last

6 @@IO_BUSY Displays the number of milliseconds spent performing IO operations since the SQL Server instance

7 @@PACK_RECEIVED Returns the total input packets read from the network since the SQL Server instance was last

8 @@PACK_SENT Returns the total output packets sent to the network since the SQL Server instance was last started

9 @@PACKET_ERRORS Displays the total network packet errors that have occurred since the SQL Server instance was

10 @@rowcount can be used to verify the success of selected operations

11 @@SERVICENAME is the Windows service used to execute and maintain the current instance of SQL Server

12 @@TIMETICKS Displays the number of microseconds per tick A tick is a unit of measurement designated by a

13 @@TOTAL_ERRORS is the total number of errors

14 @@TOTAL_READ is a count of the total read operations that have occurred since the current connection was opened

15 @@TOTAL_WRITE Displays the number of disk writes by the SQL Server instance since it was last started

16 @@VERSION VersionInformation

17 A script that creates a summary of the tables in a database

18 A stored procedure that uses the @@ERROR system function

19 Adds the account column to the authors table and creates the new bank and brokerage tables

20 Attempt to Capture @@IDENTITY, @@ROWCOUNT, and @@ERROR

21 Check existance of a view by using OBJECT_ID

22 Check the @@ERROR with if statement

23 Check the view existance

24 Check with OBJECTPROPERTY function

25 Clearing Out Memory

26 Code that tests for the existence of a temporary table

27 Connection-level exception

28 DBCC SHOW STATISTICS (tablename, index_name)

29 DBCC TRACEON

30 Displaying the Nesting Level for the Current Stored Procedure Context

31 Displaying the Oldest Active Transaction with DBCC OPENTRAN and Viewing Lock Activity

32 Dynamically building the DBCC command

33 ERROR_NUMBER functions

34 ERROR_SEVERITY function

35 EXEC and @@ROWCOUNT

36 EXEC master xp_msver

37 Flushing the Procedure Cache

38 Granting PUBLIC permissions will cause you grief

39 HashBytes function

40 HOST_NAME and HOST_ID functions give information about which workstation a particular login has come from

41 If @@ROWCOUNT = 0, return

42 If @@ROWCOUNT 1, rollback

43 Information_schema tables

44 Information_schema views

45 Match a table name in tempdb INFORMATION_SCHEMA TABLES

46 Modifying exception types with XACT_ABORT

47 OBJECT_NAME function

48 Observing line numbers in exceptions

49 Obtain metadata about database objects

50 Output the contents of the root directory of the server to the console

51 Parse exception

52 Query tempdb INFORMATION_SCHEMA TABLES

53 Reseeding the IDENTITY Value

54 Retrieving the Rows Affected by the Previous Statement

55 Returning Information about User-Defined Functions

56 Returning the Current Connections Session ID (SPID)

57 Returning the Current Database ID and Name

58 Returning the Current SQL Server Instance Name and SQL Server Version

59 SELECT 5 0

60 SELECT FROM master dbo sysmessages WHERE error = @@ERROR

61 Select HOST_ID() as Host ID

62 SELECT object_id(employee)

63 SELECT SERVERPROPERTY (Collation)

64 Select SERVERPROPERTY (Edition)

65 Select SESSION_USER

66 SELECT SESSIONPROPERTY (ANSI_PADDING) ANSI_PADDING

67 SELECT SESSIONPROPERTY (ANSI_WARNINGS) ANSI_WARNINGS

68 SELECT SESSIONPROPERTY (ARITHABORT) ARITHABORT

69 SELECT SESSIONPROPERTY (CONCAT_NULL_YIELDS_NULL) CONCAT_NULL_YIELDS_NULL

70 SELECT SESSIONPROPERTY (NUMERIC_ROUNDABORT) NUMERIC_ROUNDABO

71 SELECT SESSIONPROPERTY (QUOTED_IDENTIFIER) QUOTED_IDENTIFIER

72 SELECT TABLE_CATALOG FROM INFORMATION_SCHEMA TABLES WHERE TABLE_NAME = TestTable

73 Select top 10 from Information_schema columns

74 Sends an email when an insert or delete occurs on the discounts table

75 SET ANSI_NULLS ONOFF

76 SET QUOTED_IDENTIFIER ON

77 Show Join hints for INNER HASH JOIN

78 SUSER_NAME Function returns the name of the user logged into the server

79 Syntax for Reseeding the IDENTITY Value with a New Explicit Value

80 Syntax for Retrieving the Current IDENTITY Value of a Table and the Correct Value

81 System views INFORMATION_SCHEMA

82 The syntax of the OBJECT_ID function

83 This variable contains the complete version information for the current instance of SQL Server

84 Updating an end users expiration date

85 Use OBJECT_ID in insert statement

86 Use OBJECTPROPERTY and OBJECT_ID to check the table existance

87 Use SUSER_ID() as constraint

88 Using @@ERROR

89 Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values

90 Using Join Hints

91 Using SET LOCK TIMEOUT

92 Using SET NOCOUNT OFF to suppress count messages

93 Using the OBJECTPROPERTY Function to Check Whether a View is Indexable

94 Using the SET FORCEPLAN statement

95 Using the SET ROWCOUNT Option

96 Viewing and Setting Current Connection Lock Timeout Settings

97 Viewing the Language Used in the Current Session

98 Viewing User Connection Options

99 XACT_ABORT and transactions

100 XACT_ABORT in a stored procedure

101 Xp_msver function