Regra de automação do Jira quando a solicitação pull é mesclada
Posted by: Tim Miller
Indo além da agilidade
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.
Indo além da agilidade
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.
Indo além da agilidade
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.TABLES
The 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.COLUMNS
Or, 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.