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.
Why is This a Problem?
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.
Using the Information Schema
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 SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
0 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 SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
1 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 SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
2 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.
How to SELECT from TABLE_NAME in SQL?
When you execute the SQL statement SELECT * FROM table_name, the database engine retrieves all columns and rows from the specified table and returns them as a result set. Each row in the result set represents a record from the table, and each column contains a specific piece of data associated with that record.
How to use information_schema tables?
Using the Information Schema.
SELECT TABLE_NAME FROM INFORMATION_SCHEMA. TABLES..
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA. COLUMNS..
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA. COLUMNS WHERE TABLE_NAME = 'Album'.
IF EXISTS( SELECT * FROM INFORMATION_SCHEMA. ... .
IF EXISTS( SELECT * FROM INFORMATION_SCHEMA..
How to fix there is already an object named in the database?
Drop the database and remake it..
Delete the tables you have in the database, that would be "duplicated" by your create statements..
How to get table name in SELECT query?
The select word initiates the select command of the SQL query telling the SQL compiler that user wants to see the data..
The * right after the select word means, you want to see everything that's in the table (i.e all the column fields).
The tbl_Employees is the name of the table in which the data resides..