I will tell you below metadata functions in this article.
- APP_NAME
- COL_LENGTH
- DATABASEPROPERTYEX
- SERVERPROPERTY
- DB_ID
- DB_NAME
- OBJECT_ID
- OBJECT_NAME
- SCHEMA_ID
- SCHEMA_NAME
- STATS_DATE
APP_NAME Function:
This function returns the application name in which the session is executed.
|
1 |
SELECT APP_NAME(); |
COL_LENGTH Function:
COL_LENGHT functions returns the specified column’s lenght.
|
1 2 |
CREATE TABLE test_col_length(col1 int, col2 varchar(20)) SELECT COL_LENGTH('test_col_length','col1'), COL_LENGTH('test_col_length','col2') |
DATABASEPROPERTYEX Function:
This function returns the specified database’s option information.
|
1 |
SELECT DATABASEPROPERTYEX ('TestDB','Collation') |
You can query the following options:
- Collation
- ComparisonStyle
- Edition
- IsAnsiNullDefault
- IsAnsiNullsEnabled
- IsAnsiPaddingEnabled
- IsAnsiWarningsEnabled
- IsArithmeticAbortEnabled
- IsAutoClose
- IsAutoCreateStatistics
- IsAutoCreateStatisticsIncremental
- IsAutoShrink
- IsAutoUpdateStatistics
- IsClone
- IsCloseCursorsOnCommitEnabled
- IsFulltextEnabled
- IsInStandBy
- IsLocalCursorsDefault
- IsMemoryOptimizedElevateToSnapshotEnabled
- IsMergePublished
- IsNullConcat
- IsNumericRoundAbortEnabled
- IsParameterizationForced
- IsQuotedIdentifiersEnabled
- IsPublished
- IsRecursiveTriggersEnabled
- IsSubscribed
- IsSyncWithBackup
- IsTornPageDetectionEnabled
- IsVerifiedClone
- IsXTPSupported
- LastGoodCheckDbTime
- LCID
- MaxSizeInBytes
- Recovery
- ServiceObjective
- ServiceObjectiveId
- SQLSortOrder
- Status
- Updateability
- UserAccess
- Version
SERVERPROPERTY Function:
This function returns the specified property of the instace.
|
1 |
SELECT SERVERPROPERTY ('Collation'); |
You can query the following options:
- BuildClrVersion
- Collation
- CollationID
- ComparisonStyle
- ComputerNamePhysicalNetBIOS
- Edition
- EditionID
- EngineEdition
- HadrManagerStatus
- InstanceDefaultDataPath
- InstanceDefaultLogPath
- InstanceName
- IsAdvancedAnalyticsInstalled
- IsClustered
- IsFullTextInstalled
- IsHadrEnabled
- IsIntegratedSecurityOnly
- IsLocalDB
- IsPolyBaseInstalled
- IsSingleUser
- IsXTPSupported
- LCID
- LicenseType
- MachineName
- NumLicenses
- ProcessID
- ProductBuild
- ProductBuildType
- ProductLevel
- ProductMajorVersion
- ProductMinorVersion
- ProductUpdateLevel
- ProductUpdateReference
- ProductVersion
- ResourceLastUpdateDateTime
- ResourceVersion
- ServerName
- SqlCharSet
- SqlCharSetName
- SqlSortOrder
- SqlSortOrderName
- FilestreamShareName
- FilestreamConfiguredLevel
- FilestreamEffectiveLevel
DB_ID Function:
This function returns the database id.
|
1 2 3 |
USE master GO SELECT DB_ID() as 'master_dbid',DB_ID('TestDB') as 'TestDB_dbid' |
DB_NAME Function:
This function returns the database name of the database id.
|
1 2 3 |
USE master GO SELECT DB_NAME() as 'dbname',DB_NAME(6) as 'dbname' |
OBJECT_ID Function:
This function returns the object id of the object.
|
1 |
SELECT OBJECT_ID('MyTable') |
OBJECT_NAME Function:
This function returns the object name of the object id.
|
1 2 3 |
DECLARE @MyTableObjectid int SELECT @MyTableObjectid=object_id FROM master.sys.objects where name='MyTable'; SELECT OBJECT_NAME(@MyTableObjectid) |
SCHEMA_ID Function:
This function returns the schema id of the schema.
|
1 |
SELECT SCHEMA_ID('dbo') |
SCHEMA_NAME Function:
This function returns the schema name of the schema id.
|
1 |
SELECT SCHEMA_NAME(1) |
STATS_DATE Function:
This function returns the most recent time the statistics for a table or indexed view were updated.
|
1 2 3 |
SELECT st.name AS statistics_name, STATS_DATE(st.object_id, st.stats_id) AS most_recent_update_date FROM sys.stats st |
