More Metadata Functions

More Metadata Functions

n my last 10-Minute Solution, “Use Property Functions to Query Metadata“, I looked at OBJECTPROPERTY, one of the property functions in SQL Server. Property functions are new to SQL Server 7.0 and are used to query various details or properties about the objects in your database. In this 10-Minute Solution, I take a look at the COLUMNPROPERTY and DATABASEPROPERTY functions.

The COLUMNPROPERTY Function
As you can probably guess, the COLUMNPROPERTY function returns information about table columns, but it also returns details about stored procedure parameters. The COLUMNPROPERTY function takes three parameters: the ObjectID of the table or stored procedure, the name of the column or parameter, and the property name that is the attribute you want to query. For example, this code queries a column in the Categories table as to whether or not it allows null values:

USE NorthwindSELECT (CASE COLUMNPROPERTY(OBJECT_ID('Categories'), 'CategoryID',     'AllowsNull') WHEN 1 THEN 'True' ELSE 'False' END)     AS 'AllowsNulls'

Just like the OBJECTPROPERTY function, COLUMNPROPERTY doesn’t work across databases, so you must specify the correct database first. COLUMNPROPERTY returns True or False, except when checking for Precision or Scale—in which case it returns the precision or scale value for the column or parameter. Table 1 lists all the property attributes that can be returned with COLUMNPROPERTY, as well as which ones are specific to SQL Server 2000.

Table 1. Property Name Values Used with the COLUMNPROPERTY Function

Property

SQL Server 2000 Only

Description

AllowsNull

 

Returns whether or not the column’s nullability is set to allow null values

IsComputed

 

Returns whether or not the column is computed with the values of one or more columns

IsCursorType

 

Returns whether or not the parameter is of data type cursor

IsDeterministic

x

Returns whether or not a computed column is deterministic.

IsFulltextIndexed

 

Returns whether or not the column is used in full-text indexing

IsIdentity

 

Returns whether or not the column has its Identity attribute set for automatic numbering

IsIdNotForRepl

 

Returns whether or not an identity column’s values should be re-seeded during replication or replicated with the explicit values of the column

IsIndexable

x

Returns whether or not the column can be indexed

IsOutParam

 

Returns whether or not a stored procedure parameter is an output parameter

IsPrecise

x

The column is precise (deterministic columns only)

IsRowGuidCol

 

Returns whether or not a column is of data type uniqueidentifier and has its ROWGUID property set

Precision

 

Returns the precision for a column or parameter

Scale

 

Returns the scale for a column or parameter

UsesAnsiTrim

 

Returns whether or not the SQL-92 ANSI padding setting was on when the table was created

The DATABASEPROPERTY Function
Appropriately, the DATABASEPROPERTY function returns attributes for a database and takes two parameters: the database name and the property name. Use this syntax to query whether or not the specified database uses the SQL-92 standard for null values:

USE NorthwindSELECT DATABASEPROPERTY('Northwind', 'IsAnsiNullDefault')

Note that in SQL Server 2000, the DATABASEPROPERTY function is replaced by the DATABASEPROPERTYEX function. The old function name can still be used—but is provided for backward compatibility only—so you’ll want to update any references to the new function name. SQL Server 2000 provides quite a few new property name values and, in some cases, condenses multiple property names into a single one. For example, the IsInStandBy, IsInLoad, and IsInRecovery property names are no longer used with the DATABASEPROPERTYEX function; instead there’s a single Status property name and the function’s return value is the database status (see Table 2).

Table 2. Property Name Values Used with the DATABASEPROPERTY(EX) Function

Property

SQL Server 7.0 Only

SQL Server 2000 Only

Description

Collation

 

x

Returns the name of the default collation for the database

IsAnsiNullDefault

  

Returns whether or not the database uses the SQL-92 standard for allowing nulls

IsAnsiNullsEnabled

  

Returns whether or not comparisons that include a null value evaluate to unknown

IsAnsiPaddingEnabled

 

x

Returns whether or not string values are padded to the same length when they are compared or inserted

IsAnsiWarningsEnabled

  

Returns whether or not error and warning messages are issued when an error is encountered

IsArithmeticAbortEnabled

 

x

Returns whether or not queries are terminated if an overflow or divide-by-zero error occurs

IsAutoClose

  

Returns whether or not the database shuts down automatically after the last user exits

IsAutoCreateStatistics

 

x

Returns whether or not the statistics are updated automatically when they become out of date due to table changes

IsAutoShrink

  

Returns whether or not the database files are shrunk automatically

IsAutoUpdateStatistics

  

Returns whether or not the automatic updating of statistics is enabled on a database

IsBulkCopy

x

 

Returns whether or not the database allows non-logged operations

IsCloseCursorsOnCommitEnabled

  

Returns whether or not open cursors are closed when a transaction is committed

IsDboOnly

x

 

Returns whether or not the database is in DBO-only access mode

IsDetached

x

 

Returns whether or not the database was detached

IsEmergencyMode

x

 

Returns whether or not emergency mode is enabled so a suspect database can be used

IsFulltextEnabled

  

Returns whether or not the database is enabled for full-text indexing

IsInLoad

x

 

Returns whether or not the database is currently loading.

IsInRecovery

x

 

Returns whether or not the database is currently recovering

IsInStandBy

  

Returns whether or not the database is online as read-only

IsLocalCursorsDefault

  

Returns whether or not declared cursors are local

IsMergePublished

 

x

Returns whether or not tables in the database can be published for replication

IsNotRecovered

x

 

Returns whether or not the database failed to recover

IsNullConcat

  

Returns whether or not concatenation of a value with a null returns a null

IsNumericRoundAbortEnabled

 

x

Returns whether or not an error is generated when a loss of precision occurs

IsOffline

x

 

Returns whether or not the database is offline.

IsQuotedIdentifiersEnabled

  

Returns whether or not double quotation marks can be used on identifiers

IsReadOnly

x

 

Returns whether or not the database is read-only

IsRecursiveTriggersEnabled

 

x

Returns whether or not the recursive firing of triggers is enabled

IsShutDown

x

 

Returns whether or not the database encountered a problem at startup

IsSingleUser

x

 

Returns whether or not the database is in single-user mode

IsSubscribed

 

x

Returns whether or not the database can be subscribed for replication

IsSuspect

x

 

Returns whether or not the database is suspect

IsTornPageDetectionEnabled

 

x

Returns whether or not the database detects incomplete I/O operations

IsTruncLog

x

 

Returns whether or not the database truncates its log on checkpoints

Recovery

 

x

Returns the recovery mode for the database: FULL, BULK_LOGGED, or SIMPLE

SQLSortOrder

 

x

Returns zero for Windows collation or the ID value of the sort order

Status

 

x

Returns the database status of ONLINE, OFFLINE, RESTORING, RECOVERING, or SUSPECT

Updateability

 

x

Returns READ_ONLY for read-only databases or READ_WRITE for modifiable databases

UserAccess

 

x

Returns which users can access the database: SINGLE_USER for only one user at a time; RESTRICTED_USER for members of db_owner, dbcreator, or sysadmin roles; or MULTI_USER for all users

Version

  

Returns the internal SQL Server version number if the database is open or null if it’s closed

devx-admin

devx-admin

Share the Post:
USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India,

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted