This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysforeignkeys returns information on the columns in foreignkeys.  Each row represents the relationship between each column in foreign key relationships.  With the keyno column indicating the order of the columns within the constraint.

Sysforeignkeys is replaced by the catalog view sys.foreign_key_columns.  Like the compatibility view, the catalog view also represents each row of constraint relationships in it’s output.

Query Via sysforeignkeys

Contrary to many of the other compatibility views, the information represented in sysforeignkeys is done so quite simply.  There are columns identifying the relationship, parent, and child tables.  And then columns for the rows in the parents and columns that are related and their order.  The query in Listing 1 is one commonly used to retrieve information from sysforeignkeys.

--Listing 1 – Query for sys.sysforeignkeys

SELECT constid
,fkeyid
,rkeyid
,fkey
,rkey
,keyno
FROM sysforeignkeys

Query via sys.foreign_key_columns

As simple as sysforeignkeys is to access, sys.foreign_key_columns is just as easy.  Save for the renaming of the columns, the information returned is identical in all other respects.  Using the query in Listing 2, the same information on foreign keys can be retrieved.  The one thing a little more simple about sys.foreign_key_columns is the added clarity in the name of the catalog view and the names of the columns

--Listing 2 – Query for sys.foreign_key_columns

SELECT constraint_object_id AS constid
,parent_object_id AS fkeyid
,referenced_object_id AS rkeyid
,constraint_column_id AS fkey
,parent_column_id AS rkey
,referenced_column_id AS keyno
FROM sys.foreign_key_columns

Summary

In this post, the use of sys.foreign_key_columns was discussed as an alternative to sysforeignkeys.  With a very small amount of code change, the upgrade from one to the other is quite simple.  After reading all of this, do you see any reason to continue using sysforeignkeys?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Follow me on Twitter at StrateSQL.

This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysfiles returns one row for every file that is associated with a database.  The data returned is scoped at the database level.

The catalog view sys.database_files is the replacement for sysfiles.  Like sysfiles, sys.database_files returns one row for every file that is associated to a database.

Status Column

The compatibility view contains a single status column.  According to Books Online, there are only three properties exposed through the status column.  A little research, though, indicates there are more than those stored within the column.  The values that have been uncovered so far are:

• 0×1:Default device (no longer used)
• 0×2:On disk file
• 0×10:Indicates if media is read only
• 0×40:Indicates if file is the transaction log
• 0×80:Indicates if file has been written to since last backup
• 0×1000:Indicates if file is read only
• 0×4000:Indicates whether device was create implicitly by CREATE DATABASE
• 0×8000:Indicates whether device created during CREATE DATABASE
• 0×100000:Indicates whether the growth rate is a percentage
• 0×10000000:Indicates if the file contains sparse data
• 0×20000000: Indicate if the file is offline

Query Via sysfiles

When querying the data from sysfiles, there are a few things that need to be taken into account.  First, there are a number of values stuffed into the status column.  These values need to be retireved to understand all of the properties for the files.  Second, while there are a number of values, not all of them are in use any longer and refer more to constructs that are no longer a part of SQL Server today, such as media devices for databases.  Along with that, some methods of identifying the state of a file, such as device_created_implicitly_by_create_database, are vague and may not be well suited as a flagged item.  The last item is the perf column, this column is reserved for internal use and appears as a 0 in all examples found. The query to retrieve the information from sysfiles can be found in Listing 1.

--Listing 1 – Query for sysfiles
SELECT fileid
,groupid
,size
,maxsize
,growth
,name
,filename
,CONVERT(INT,status & 0x10) / 16 is_media_read_only
,CONVERT(INT,status & 0x1000) / 4096 is_read_only
,CONVERT(INT,status & 0x100000) / 1048576 is_percent_growth
,CONVERT(INT,status & 0x10000000) / 268435456 is_sparse
,CONVERT(INT,status & 0x40) / 64 is_transaction_log
,CONVERT(INT,status & 0x20000000) / 536870912 is_offline
,CONVERT(INT,status & 0x1) / 1 default_device
,CONVERT(INT,status & 0x2) / 2 disk_file
,CONVERT(INT,status & 0x80) / 128 is_file_written_to_since_backup
,CONVERT(INT,status & 0x4000) / 16384 device_created_implicitly_by_create_database
,CONVERT(INT,status & 0x8000) / 32768 device_created_during_database_creation
,status
,perf
FROM sysfiles

Query via sys.database_files

Since the catalog view view sys.database_files presents the previously mentioned status columns directly in the view, the query for this information is quite simple.  As shown in the query in Listing 2, most of the columns from sysfiles are present in the query. There are some exceptions to this though.  First, the perf, media, and device details, which are obsolete, are no longer represented.  Second, instead of using flag to represent things like device created during CREATE DATABASE, there are columns that indicate the LSN of when these events occurred.  In total there are twelve LSN events tracked that can provide the information previously flagged but with greater granularity.

--Listing 2 – Query for sys.database_files
SELECT file_id
,file_guid
,data_space_id as groupid
,size
,max_size
,growth
,name
,physical_name
,is_media_read_only
,is_read_only
,is_percent_growth
,is_sparse
,type
,type_desc
,state
,state_desc
,is_name_reserved
,create_lsn
,drop_lsn
,read_only_lsn
,read_write_lsn
,differential_base_lsn
,differential_base_guid
,differential_base_time
,redo_start_lsn
,redo_start_fork_guid
,redo_target_lsn
,redo_target_fork_guid
,backup_lsn
FROM sys.database_files

Summary

In this post, we discussed the use of sys.database_files instead of sysfiles.  While the information presented is quite simple, there are some nuances with some of the flags and ways in which SQL Server has changed how the files are organized.  But with anything using sysfiles with SQL Server 2012, the obsolete properties shouldn’t be an issue.  After reading all of this, do you see any reason to continue using sysfiles?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Follow me on Twitter at StrateSQL.