The SQL Server system views/tables/functions. Common questions and solutions to real life problems

Introduction

In this new article, we will talk about the system views/tables/functions and how to solve common questions using them.

The system views are views that contain internal information about a Database.

The master database for example contains information about the SQL Server itself, while the msdb database contain information about the SQL Server agent and each database has its own system views/tables.

In this article we will show how to get the list of tables, views, stored procedures, how to get a list of tables of all the databases, how to find a table in multiple datatabases, how to get the list of users, logins, mapped logins, how to detect a fragmentation in a table and more.

Let’s start with some common questions about databases that can be solved with the system views/tables/functions:

How can I get the list of tables in a database?

The following queries can provide you with that information about the database tables:

Option 1

SELECT * FROM [INFORMATION_SCHEMA].[TABLES]

Option 2

SELECT *

FROM sysobjects WHERE xtype = 'U'

You can use schema views (the INFORMATION_CHEMA.TABLES) or the sysobjects views directly. According to the books, it is better to use the INFORMATION_SCHEMA views because the internal structure will not change in the future. The sysobjects view contains useful information about the different database objects.

Xtype is the type of object and the possible values are:

SQ = Service Queue
TA = Assembly (CLR) DML trigger
TF = Table-valued-Function
TR = Trigger
TT = Table Type
U = User Table
UQ = Unique Constraint
V = View
X = Extended stored procedure

For more information about the sysobjects view you can go to the references.

How can I get the list of views in a database?

The solution is similar than the list of tables. There are 2 options to solve this problem:

Option 1

SELECT *

FROM [INFORMATION_SCHEMA].[VIEWS]

Option 2

SELECT *

FROM sysobjects

WHERE xtype = 'V'

[INFORMATION_SCHEMA].[VIEWS] contains information about the views or you can find the information in the sysobjects view.

How can I get the list of procedures in a database?

There are 2 options to solve this problem:

select * from [INFORMATION_SCHEMA].[ROUTINES]

where routine_type='PROCEDURE'

SELECT *

FROM sysobjects

WHERE xtype = 'P'

[INFORMATION_SCHEMA].[ROUTINES] contains information about stored procedures and functions. The sysobjects is a second option to get this information.

How can I get the creation date of a specific table?

The following T-SQL code shows the creation date of the table ProductDocument:

SELECT crdate

FROM sysobjects

WHERE xtype = 'U'

and

name ='ProductDocument'

Crdate is the creation date and sysobjects is the view that contains most of the database objects including tables, views, stored procedures and functions.

How can I get the list of all the tables in all the databases?

The sp_MSforeachdb, is a very useful stored procedure that helps to work with all the databases. For some reason, this stored procedure is not documented.

DECLARE @cmd varchar(8000)

SELECT @cmd = 'USE ? SELECT ''?'',name FROM sysobjects WHERE xtype = ''U'''

EXEC sp_MSforeachdb @cmd

The ? is the database name. It will show the result of the query in each database.

How can I search a table in all the databases?

This is a classical problem. The user created a table, but he does not remember where it was created and there are multiple databases where we need to search.

The following example shows how to find a table named test in all the SQL Server databases. The query will show the database(s) and the table.

DECLARE @cmd varchar(8000)

SELECT @cmd = 'USE ? SELECT ''?'',name FROM sysobjects WHERE xtype = ''U'' and name=''test'''

EXEC sp_MSforeachdb @cmd

How can I get the list of stored procedures names and their code?

The syscomments view contains very useful information about the stored procedure, triggers, checks and other SQL Server objects. You can check the code of your procedures, triggers and other objects. The sysobjects contains the stored procedure name and the syscomments the code. This query is very useful to find some words in multiple stored procedures at the same time.

SELECT name, [text]

FROM sysobjects o

INNER JOIN syscomments c

o.id=c.id

WHERE xtype = 'P'

How can I get the creation date of a specific database user?

The sysusers view contain useful information related to the users in a database. The following sample shows the creation date of the public database user.

select createdate from [sys].[sysusers]

where name='public'

For more information about the sysusers, go to references.

How can I get the creation date of a specific SQL Server Login?

The Login allows to login to the SQL Server database and the database user contains permissions to access to specific databases. The following query shows the creation Date of a specific Login:

select * from

[sys].[syslogins]

where name='sa'

How can I get the Login and the user mapped in all the databases?

This information is in the sys.database.principals and syss,server.principals. You will find the Login and the database user mapped to it.

DECLARE @cmd varchar(8000)

SELECT @cmd =

'SELECT ''?'',login_name = sp.name, user_name = dp.name

FROM ?.sys.database_principals AS dp

INNER JOIN sys.server_principals AS sp

ON dp.[sid] = sp.[sid]'

EXEC sp_MSforeachdb @cmd

How can I detect the fragmentation of a specific table?

Multiple inserts, deletes can produce fragmentations in the databases. In earlier versions, the DBCC SHOWCONTIG sentences were used to detect fragmentations. Now, we use the sys.dm_db_index_physical_stats function. The column that you need to check is the avg_fragmentation_in_percent. If the percentage is high, you may need to reorganize or rebuild the indexes of your table.

The following sample with shows the fragmentation information about the [Purchasing].[PurchaseOrderDetail table.

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2012');

SET @object_id = OBJECT_ID('[Purchasing].[PurchaseOrderDetail]');

SELECT avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

If the fragmentation percentage is higher than 30%, it is better to rebuild the index. If it is lower than 30%, you can just reorganize the index.

How can I get the information about the SQL Server sessions?

The [sys].[dm_exec_sessions] view contains useful information about the sessions like the start time, end time, login name, NT domain, program used and more.

SELECT *

FROM [sys].[dm_exec_sessions]

How can I get information about the creation date of a backup?

You can find the start date and end date of a database backup in the backupset table.

SELECT

[backup_start_date],

[backup_finish_date]

FROM [msdb].[dbo].[backupset]

How can I get information about the size in MB and location of a backup?

You can also find very useful information about the backup file size and the path of the backup using the backupfile system table. The following sample shows the size of the backups in MB and the path where it is located. This information is stored in the msdn database. The backup file_size is stored in bytes, that is why it is necessary to convert the value to MB.

SELECT

[file_size]/1024/1024 as Size_in_mb

,[physical_name]

FROM [msdb].[dbo].[backupfile]

How can I get information about creation date and modification date of a SQL Server Job?

There is a lot of information about SQL jobs in the MSDB database, dbo.sysjobs table. All the information related to the agent is stored in the MSDB database.

SELECT

[date_created]

,[date_modified]

FROM [msdb].[dbo].[sysjobs]

This information can also be displayed using the help_job stored procedure:

Conclusion

The system/table/functions helps us to monitor our database, track changes and measure the performance of the databases. There are multiple uses of these objects to help us in our real life problems.

Author
Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

PostgreSQL tutorial to create a user - November 12, 2023
PostgreSQL Tutorial for beginners - April 6, 2023
PSQL stored procedures overview and examples - February 14, 2023

Introduction

How can I get the list of tables in a database?

How can I get the list of views in a database?

How can I get the list of procedures in a database?

How can I get the creation date of a specific table?

How can I get the list of all the tables in all the databases?

How can I search a table in all the databases?

How can I get the list of stored procedures names and their code?

How can I get the creation date of a specific database user?

How can I get the creation date of a specific SQL Server Login?

How can I get the Login and the user mapped in all the databases?

How can I detect the fragmentation of a specific table?

How can I get the information about the SQL Server sessions?

How can I get information about the creation date of a backup?

How can I get information about the size in MB and location of a backup?

How can I get information about creation date and modification date of a SQL Server Job?

Conclusion

Related posts: