SQL Server Metadata Functions

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.

COL_LENGTH Function:

COL_LENGHT functions returns the specified column’s lenght.

DATABASEPROPERTYEX Function:

This function returns the specified database’s option information.

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.

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.

DB_NAME Function:

This function returns the database name of the database id.

OBJECT_ID Function:

This function returns the object id of the object.

OBJECT_NAME Function:

This function returns the object name of the object id.

SCHEMA_ID Function:

This function returns the schema id of the schema.

SCHEMA_NAME Function:

This function returns the schema name of the schema id.

STATS_DATE Function:

This function returns the most recent time the statistics for a table or indexed view were updated.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *