Regola di automazione di Jira quando una pull request viene sottoposta a merge
Posted by: Tim Miller
Andare oltre Agile
When writing queries for a database you might be new to, or one that changes often, you might want to run a quick check to find all the tables in a specific database, or the columns in the database, or to search if table or column exists.
Andare oltre Agile
Understanding the schema and what tables are in it help to write efficient SQL and helps avoid running queries multiple times just to see if the schema name or column name is correct.
This tutorial will help solve these problems.
Andare oltre Agile
Querying the metadata on a data source is the easiest way to determine the makeup of a table if you don’t have an understanding of it already. Microsoft SQL Server provides an information schema view as one of several methods for obtaining this metadata. As their support documentation states, “Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables.”
We will be using a couple of the views in the information schema in order to run queries that help determine the makeup of tables in the data source.
To Show the TABLES and COLUMNS in the database or find TABLES and COLUMNS.
This first query will return all of the tables in the database you are querying.
SELECT
TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLESThe second query will return a list of all the columns and tables in the database you are querying.
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNSOr, you can also query for just the COLUMNS from a specific table and return the column names from the specific table ‘Album’ in our database.
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Album'With this next query you can find out whether or not there is a TABLE in the data source that matches some kind of search parameters.
IF EXISTS(
SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME = 'Album'
)
SELECT 'found' AS search_result ELSE SELECT 'not found' AS search_result;The query will return the word ‘found’ if the table ‘Album’ exists in our database.
Now to take it a littler further, you can use this query to find out whether or not there is a COLUMN in the data source that matches some kind of search parameters.
IF EXISTS(
SELECT
*
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME = 'Title'
)
SELECT 'found' AS search_result ELSE SELECT 'not found' AS search_result;Utilizing the INFORMATION_SCHEMA view in your data source can be a reliable way to determine what is in the data source while you build your queries.
For more information on the Microsoft SQL Server System Information Schema Views, please read more from their support docs.