View all functions from the SQL Server database?_问答_开发者

How can I view all the functions (built in) in a SQL Server database using SQL Server Ma开发者_开发知识库nagement Studio?

This will return all user-defined functions. I'm not sure what you mean by "build-in" functions.

SELECT * 
FROM sys.objects 
WHERE RIGHT(type_desc, 8) = 'FUNCTION'

SELECT * FROM sys.all_objects where type in ('FN','AF','FS','FT','IF','TF')

Here are the types:

--AF = Aggregate function (CLR)
--C = CHECK constraint
--D = DEFAULT (constraint or stand-alone)
--F = FOREIGN KEY constraint
--PK = PRIMARY KEY constraint
--P = SQL stored procedure
--PC = Assembly (CLR) stored procedure
--FN = SQL scalar-function
--FS = Assembly (CLR) scalar function
--FT = Assembly (CLR) table-valued function
--R = Rule (old-style, stand-alone)
--RF = Replication filter procedure
--SN = Synonym
--SQ = Service queue
--TA = Assembly (CLR) trigger
--TR = SQL trigger 
--IF = SQL inlined table-valued function
--TF = SQL table-valued function
--U = Table (user-defined)
--UQ = UNIQUE constraint
--V = View
--X = Extended stored procedure
--IT = Internal table

Here is a list of all system stored procs:

http://msdn.microsoft.com/en-us/library/ms187961.aspx

Not on the SSMS, but in TSQL

select * from master..sysobjects where type = 'fn'

But do you mean also DMV's, sprocs etc?

Microsoft doesn't seem to have any public list of built-in functions in T-SQL or SQL Server, but you can reconstruct such a list by using their documentation's function-list (from the table-of-contents) and your web-browser's dev-tools window.

Open a Chromium-based browser and navigate to the "Functions > Functions" page.
- As of October 2022, that's https://learn.microsoft.com/en-us/sql/t-sql/functions/functions
Expand all of the sub-category nodes under "Functions" in the table of contents.
- Left-click the arrow to expand the node, but don't click on the text itself (as that will navigate-away from the current page).
- So you should have something like this (click to zoom):
Open your browser's Developer Tools window.
Use the "Select an element in the page" feature and click on the top-level "Functions" node, like so:
The node will now be selected in the Elements tab, so then right-click the node and choose "Store as global variable".
- Like so:

Assuming it saved it to temp1 then run this in the console:

(You can copy+paste multiple statements/lines into the console, that's fine)

var lis  = Array.from( temp1.querySelectorAll(':scope > ul.tree-group > li.tree-item > ul.tree-group > li') );
var lis2 = lis.map( li => ( { li, first: li.parentElement.querySelector(':scope > li:first-child > a') } ) ).filter( t => t.li.textContent !== t.first.textContent );
var texts = lis2.map( t => ( { name: t.li.textContent, category: t.first.textContent } ) );
var joined = texts.map( t => t.category + ": " + t.name ).join( "\r\n" );

Then run console.log( joined ); to get the list of functions as text.

...or use this list:

Note this list includes some newly-added functions only available in SQL Server 2022 (such as GREATEST) or those only available in Azure Synapse Analytics and Analytics Platform System (formerly known as Parallel Data Warehouse), such as VERSION.

Aggregate:

    APPROX_COUNT_DISTINCT
    APPROX_PERCENTILE_CONT
    APPROX_PERCENTILE_DISC
    AVG
    CHECKSUM_AGG
    COUNT
    COUNT_BIG
    GROUPING
    GROUPING_ID
    MAX
    MIN
    STDEV
    STDEVP
    SUM
    VAR
    VARP

Analytic:

    CUME_DIST
    FIRST_VALUE
    LAG
    LAST_VALUE
    LEAD
    PERCENTILE_CONT
    PERCENTILE_DISC
    PERCENT_RANK

Bit manipulation:

    LEFT_SHIFT
    RIGHT_SHIFT
    BIT_COUNT
    GET_BIT
    SET_BIT

Collation

    COLLATIONPROPERTY
    TERTIARY_WEIGHTS

Configuration:
    
    @@DBTS
    @@LANGID
    @@LANGUAGE
    @@LOCK_TIMEOUT
    @@MAX_CONNECTIONS
    @@MAX_PRECISION
    @@NESTLEVEL
    @@OPTIONS
    @@REMSERVER
    @@SERVERNAME
    @@SERVICENAME
    @@SPID
    @@TEXTSIZE
    @@VERSION

Conversion:

    CAST
    CONVERT
    PARSE
    TRY_CAST
    TRY_CONVERT
    TRY_PARSE

Cryptographic:

    ASYMKEY_ID
    ASYMKEYPROPERTY
    CERTPROPERTY
    CERT_ID
    CRYPT_GEN_RANDOM
    DECRYPTBYASYMKEY
    DECRYPTBYCERT
    DECRYPTBYKEY
    DECRYPTBYKEYAUTOASYMKEY
    DECRYPTBYKEYAUTOCERT
    DECRYPTBYPASSPHRASE
    ENCRYPTBYASYMKEY
    ENCRYPTBYCERT
    ENCRYPTBYKEY
    ENCRYPTBYPASSPHRASE
    HASHBYTES
    IS_OBJECTSIGNED
    KEY_GUID
    KEY_ID
    KEY_NAME
    SIGNBYASYMKEY
    SIGNBYCERT
    SYMKEYPROPERTY
    VERIFYSIGNEDBYCERT
    VERIFYSIGNEDBYASYMKEY

Cursor:

    @@CURSOR_ROWS
    @@FETCH_STATUS
    CURSOR_STATUS

Data type:

    DATALENGTH
    IDENT_CURRENT
    IDENT_INCR
    IDENT_SEED
    IDENTITY
    SQL_VARIANT_PROPERTY

Date & time:
    
    @@DATEFIRST
    CURRENT_TIMESTAMP
    CURRENT_TIMEZONE
    CURRENT_TIMEZONE_ID
    DATE_BUCKET
    DATEADD
    DATEDIFF
    DATEDIFF_BIG
    DATEFROMPARTS
    DATENAME
    DATEPART
    DATETIME2FROMPARTS
    DATETIMEFROMPARTS
    DATETIMEOFFSETFROMPARTS
    DATETRUNC
    DAY
    EOMONTH
    GETDATE
    GETUTCDATE
    ISDATE
    MONTH
    SMALLDATETIMEFROMPARTS
    SWITCHOFFSET
    SYSDATETIME
    SYSDATETIMEOFFSET
    SYSUTCDATETIME
    TIMEFROMPARTS
    TODATETIMEOFFSET
    YEAR

Graph:
    
    EDGE_ID_FROM_PARTS
    GRAPH_ID_FROM_EDGE_ID
    GRAPH_ID_FROM_NODE_ID
    NODE_ID_FROM_PARTS
    OBJECT_ID_FROM_EDGE_ID
    OBJECT_ID_FROM_NODE_ID

JSON:
    
    ISJSON
    JSON_OBJECT
    JSON_ARRAY
    JSON_VALUE
    JSON_QUERY
    JSON_MODIFY
    JSON_PATH_EXISTS

Mathematical:
    
    ABS
    ACOS
    ASIN
    ATAN
    ATN2
    CEILING
    COS
    COT
    DEGREES
    EXP
    FLOOR
    LOG
    LOG10
    PI
    POWER
    RADIANS
    RAND
    ROUND
    SIGN
    SIN
    SQRT
    SQUARE
    TAN

Logical:
    
    CHOOSE
    GREATEST
    IIF
    LEAST

Metadata:

    @@PROCID
    APP_NAME
    APPLOCK_MODE
    APPLOCK_TEST
    ASSEMBLYPROPERTY
    COL_LENGTH
    COL_NAME
    COLUMNPROPERTY
    DATABASEPROPERTYEX
    DB_ID
    DB_NAME
    FILE_ID
    FILE_IDEX
    FILE_NAME
    FILEGROUP_ID
    FILEGROUP_NAME
    FILEGROUPPROPERTY
    FILEPROPERTY
    FILEPROPERTYEX
    FULLTEXTCATALOGPROPERTY
    FULLTEXTSERVICEPROPERTY
    INDEX_COL
    INDEXKEY_PROPERTY
    INDEXPROPERTY
    NEXT VALUE FOR
    OBJECT_DEFINITION
    OBJECT_ID
    OBJECT_NAME
    OBJECT_SCHEMA_NAME
    OBJECTPROPERTY
    OBJECTPROPERTYEX
    ORIGINAL_DB_NAME
    PARSENAME
    SCHEMA_ID
    SCHEMA_NAME
    SCOPE_IDENTITY
    SERVERPROPERTY
    STATS_DATE
    TYPE_ID
    TYPE_NAME
    TYPEPROPERTY
    VERSION

Ranking:

    DENSE_RANK
    NTILE
    RANK
    ROW_NUMBER

Replication:

    PUBLISHINGSERVERNAME

Security:

    CERTENCODED
    CERTPRIVATEKEY
    CURRENT_USER
    DATABASE_PRINCIPAL_ID
    HAS_DBACCESS
    HAS_PERMS_BY_NAME
    IS_MEMBER
    IS_ROLEMEMBER
    IS_SRVROLEMEMBER
    LOGINPROPERTY
    ORIGINAL_LOGIN
    PERMISSIONS
    PWDENCRYPT
    PWDCOMPARE
    SESSION_USER
    SESSIONPROPERTY
    SUSER_ID
    SUSER_NAME
    SUSER_SID
    SUSER_SNAME
    SYSTEM_USER
    USER
    USER_ID
    USER_NAME

String:

    ASCII
    CHAR
    CHARINDEX
    CONCAT
    CONCAT_WS
    DIFFERENCE
    FORMAT
    LEFT
    LEN
    LOWER
    LTRIM
    NCHAR
    PATINDEX
    QUOTENAME
    REPLACE
    REPLICATE
    REVERSE
    RIGHT
    RTRIM
    SOUNDEX
    SPACE
    STR
    STRING_AGG
    STRING_ESCAPE
    STUFF
    SUBSTRING
    TRANSLATE
    TRIM
    UNICODE
    UPPER

System:

    $PARTITION
    @@ERROR
    @@IDENTITY
    @@PACK_RECEIVED
    @@ROWCOUNT
    @@TRANCOUNT
    BINARY_CHECKSUM
    CHECKSUM
    COMPRESS
    CONNECTIONPROPERTY
    CONTEXT_INFO
    CURRENT_REQUEST_ID
    CURRENT_TRANSACTION_ID
    DECOMPRESS
    ERROR_LINE
    ERROR_MESSAGE
    ERROR_NUMBER
    ERROR_PROCEDURE
    ERROR_SEVERITY
    ERROR_STATE
    FORMATMESSAGE
    GET_FILESTREAM_TRANSACTION_CONTEXT
    GETANSINULL
    HOST_ID
    HOST_NAME
    ISNULL
    ISNUMERIC
    MIN_ACTIVE_ROWVERSION
    NEWID
    NEWSEQUENTIALID
    ROWCOUNT_BIG
    SESSION_CONTEXT
    SESSION_ID
    XACT_STATE

System Statistical:

    @@CONNECTIONS
    @@CPU_BUSY
    @@IDLE
    @@IO_BUSY
    @@PACK_SENT
    @@PACKET_ERRORS
    @@TIMETICKS
    @@TOTAL_ERRORS
    @@TOTAL_READ
    @@TOTAL_WRITE

Text & Image:

    TEXTPTR
    TEXTVALID

Trigger:

    COLUMNS_UPDATED
    EVENTDATA
    TRIGGER_NESTLEVEL
    UPDATE

To get all User Defined function of a particular database use the below code

USE AdventureWorks;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO