Règle d'automatisation Jira pour le merge d'une pull request
Posted by: AJ Welch
Depending on the version of SQL Server you are running, the method for querying and returning a list of all user-created tables may differ slightly. Below we’ll briefly examine the TSQL statements that can be used to retrieve a table list for both the older SQL Server 2000 and the newer 2005 version or beyond.
Au-delà d'Agile
For older versions of SQL Server (such as SQL Server 2000, though this method is also supported in SQL Server 2005 for backward compatibility), you’ll need to query the SYS.SYSOBJECTS metadata view. SYS.SYSOBJECTS contains a row for every object that has been created in the database, including stored procedures, views, and user tables (which are an important to distinguish from system tables.)
The SYSOBJECTS table houses a couple dozen columns of data since it must hold information about virtually everything added to the server over time. Therefore, to find a list of user-created tables (thus ignoring system tables), we’ll need to find results where the xtype column (which specifies the object type for that row) is equal to the value U, which stands for user table. The resulting TSQL statement should look like this:
SELECT
*
FROM
SYSOBJECTS
WHERE
xtype = 'U';
GONote: Since SYSOBJECTS are part of the SYS namespace, when querying SYSOBJECTS it is unnecessary to specify that SYSOBJECTS is part of the global SYS namespace, so we can omit it as shown in the example above.
This will return a result list of all user-created tables. Since the amount of information returned when querying all columns is quite large, you may wish to trim the results by viewing only the name column and perhaps the crdate (date of creation):
SELECT
name,
crdate
FROM
SYSOBJECTS
WHERE
xtype = 'U';
GOAu-delà d'Agile
Listing all the tables in SQL server when using a newer version (SQL 2005 or greater) is a matter of querying the INFORMATION_SCHEMA views which are automatically built into SQL Server. These allow you to easily view a wide variety of metadata for this particular SQL Server instance, including information about COLUMNS, ROUTINES, and even TABLES.
You may notice that there are four columns returned when using the INFORMATION_SCHEMA.TABLES view, but the most important column is TABLE_TYPE, which determines whether the table in that row is an actual table (BASE TABLE) or a view (VIEW).
To return all tables and views in one query, execute the following TSQL statement:
SELECT
*
FROM
INFORMATION_SCHEMA.TABLES;
GOIt may also be wise to specify the database you wish to query:
SELECT
*
FROM
databaseName.INFORMATION_SCHEMA.TABLES;
GOIf you only wish to retrieve actual tables and filter out views from the results, add a WHERE TABLE_TYPE = 'BASE TABLE' clause:
SELECT
*
FROM
databaseName.INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE';
GO