Common SQL server functions/stored procedures/database roles

/* Date function */
Dateadd (datepart, number, date)
-- Returns a new datetime value based on a specified date.
Datediff (datepart, startdate, enddate)
-- Returns the number of date and time boundaries across two specified dates.
Datename (datepart, date)
-- Returns a string representing the specified date part of the specified date.
Datepart (datepart, date)
-- Returns an integer that represents the specified date of the specified date.
Day (date)
-- Returns an integer that represents the day of the specified date.
Getdate ()
-- Microsoft by datetime value? SQL Server? The standard internal format returns the current system date and time.
Getutcdate ()
-- Returns the datetime value that represents the current UTC time (World Time Coordinate or Greenwich Mean Time.
-- The current UTC time is based on the current local time and the time zone settings in the operating system of the computer that runs SQL Server.
Month (date)
-- Returns an integer representing the specified date and month.
Year (date)
-- Returns an integer that represents the year of the specified date.
--------------------------------------------------------------------------
/* String processing functions */
Lcase ()
Lower ()
-- Converts a string to lowercase letters.
Ltrim ()
-- Delete spaces before a string
Substring ()
-- Extract one or more characters from a string
Ucase ()
Upper ()
-- Converts a string to uppercase letters.
Round ()
-- Rounds the number to a specified decimal point.
Floor ()
-- Rounds down the number to the nearest (minimum) integer.
Ceiling ()
-- Rounds up the number to the nearest integer.
Datalength ()
-- Returns the number of bytes used by the specified expression.
--------------------------------------------------------------------------
User ()
User_name ()
-- Returns the current user name.
Convert ()
-- Convert data from one type to another.
Soundex ()
-- Return the soundex code for a specified expression that can create an "Approximate" search.
STR ()
-- Converts numeric data to a string so that it can be processed using text operators.
/* Global variable */
@ Connections
-- Number of connections created since the server was last started
@ Cpu_busy
-- The number of milliseconds that the system has been running since SQL Server was started.
@ Cursor_rows
-- Number of rows in the recently opened cursor
@ Datefirst
The current value of the -- set datefirst parameter, which is used to set the day of the first day of a week.
@ Error
-- The last T-SQL Error error code
@ Fetch_status
-- If the last extracted state is successful, the value is 0. -1 if an error occurs
@ Identity
-- Id value of the last insert operation
@ Language
-- Name of the currently used language
@ Max_connections
-- Maximum number of simultaneous connections that can be created
@ Rowcount
-- Number of rows affected by the previous SQL statement
@ Servername
-- Name of the local server
@ Servicename
-- Name of the SQL service on the computer
@ Timeticks
-- The number of microseconds per instruction period on the current computer
@ Transcount
-- Number of transactions opened by the current connection
@ Version
-- SQL Server version
-----------------------------------------------------------------------
/* Stored Procedure */
Sp_databases -- list all databases on the server
Sp_server_info -- list server information, such as character set, version, and order
Sp_stored_procedures -- list all stored procedures in the current environment
Sp_tables -- list all objects that can be queried in the current environment
Sp_start_job -- start the automation task immediately
Sp_stop_job -- stop an automation task in progress
Sp_password -- add or modify the Logon account password
Sp_configure -- display (without options) or change (with options) The global configuration settings of the current server
Sp_help -- return the column name, data type, and constraint type of the table.
Sp_helptext -- display rules, default values, unencrypted stored procedures, user-defined functions,
-- Actual text of a trigger or view
Sp_helpfile -- view the current database information
Sp_dboption -- display or Change Database options
Sp_detach_db -- detach a database
Sp_attach_db -- Additional database
Sp_addumpdevice -- add a device
Sp_dropdevice -- delete a device
Sp_pkeys -- view the primary key
Sp_fkeys -- View Foreign keys
Sp_helpdb -- view the file information of a specified database
Sp_addtype -- user-created data type
Sp_droptype -- delete user-created data types
Sp_rename -- rename the database
Sp_executesql -- execute an SQL statement
Sp_addlogin -- add Logon
Sp_droplogin -- delete Logon
Sp_grantdbaccess -- Maps users to logon, that is, adding a database security account and granting tower Access Permissions
Sp_revokedbaccess -- revokes the user's data access permission, that is, deleting a security account from the database
Sp_addrole -- add a role
Sp_addrolemember -- add a member to the role to become a member of the database role.
SP_ADDSRVROLEMEMBER -- modify logon to make it a member of a fixed server role
Sp_grantlogin -- allows a group account or a system user to connect to SQL using Windows Authentication
Sp_defaultdb -- modify a default database for Logon
Sp_helpindex -- used to view the table Index
Sp_cursoropen -- Define SQL statements related to the cursor and cursor options, and then generate the cursor
Sp_cursorfetch -- extract one or more rows from the cursor
Sp_cursorclose -- close and release the cursor
Sp_cursoroption -- set various cursor options
Sp_cursor -- used to request location update
Sp_cursorprepare -- compile a cursor-related T-SQL statement or batch into an execution plan without creating a cursor
Sp_cursorexecute -- create and fill the cursor in the execution plan created by sp_cursorprepare
Sp_cursorunprepare -- discard the execution plan generated by sp_cursorprepare
Sp_settriggerorder -- specifies the first or last after trigger associated with the table. In the first
-- The after trigger triggered between the trigger and the last trigger will be executed in an undefined order.
--------------------------------------------------------------------------------
/* Server role */
SysAdmin
-- Perform any activity in SQL Server. This role's permissions span all other fixed server roles.
Serveradmin
-- Configure the server range settings.
Setupadmin
-- Add or delete a linked server and execute some system stored procedures (such as sp_serveroption ).
Securityadmin
-- Manage Server logon.
Processadmin
-- Manage processes running in an SQL server instance.
Dbcreator
-- Create and Change databases.
Diskadmin
-- Manage disk files.
Bulkadmin
-- Execute the bulk insert statement.
/* Database role */
Public
Public role
-- The public role is a special database role. Each database user belongs to it. Public role:
-- Capture all the default permissions of users in the database.
-- Users, groups, or roles cannot be assigned to it, because they belong to this role by default.
-- Contains each database, including master, MSDB, tempdb, model, and all user databases.
-- Cannot be removed.
Db_owner
-- Activities of all database roles and other maintenance and configuration activities in the database.
-- This role's permissions span all other fixed database roles.
Db_accessadmin
-- Add or delete Windows NT 4.0 or Windows 2000 Groups and users and SQL Server users in the database.
Db_datareader
-- View all data from all user tables in the database.
Db_datawriter
-- Add, modify, or delete data from all user tables in the database
Db_ddladmin
-- Add, modify, or remove objects in the database (run all DDL statements)
Db_securityadmin
-- Manage Roles and members of the SQL Server 2000 database role and manage statements and object permissions in the database
Db_backupoperator
-- Database Backup permission
Db_denydatareader
-- The permission to select database data is denied.
Db_denydatawriter
-- Refuse to change database data Permissions