Basic SQL Commands on Tables in SQL Server

Create a Table

To “Create” a “New Table” inside a “Database”, the “CREATE TABLE <table-name> (Col1 Datatype, Col2 Datatype, ….)” Command is used.

How to Verify If a Table Already Exists

The following “Five Approaches” are used to Verify if a Table Exists in a Database in SQL Server -

A. Using “SYS.TABLES” System Catalog View - Querying the “SYS.TABLES” System Catalog View returns a Row for “Each User Table”.

It is also possible to “Verify” if a “Given Table” “Exists” Inside a Particular Schema. In that case, if the “SCHEMA_ID” is known that can be provided in the WHERE clause. Otherwise, since the “SYS.TABLES” System Catalog View only returns the “Schema ID”, that Column can be Passed to the “SCHEMA_NAME ()” Function in order to get the Name.

B. Using “SP_TABLES” System Stored Procedure - The System Stored Procedure “SP_TABLES” returns the information of the “Table” and the “View” by the “Given Name”.

“TABLE_QUALIFIER” is the Name of the “Database” the “Given Table” is Created In.

“TABLE_OWNER” is the Name of the “Schema” the “Given Table” is Created Inside.

Since, the System Stored Procedure “SP_TABLES” also returns the information of the “View” by the “Given Name”, it is a good idea to narrow the Result down just to “Table” using the Condition “@TABLE_TYPE = “TABLE””.

The “@TABLE_TYPE” Parameter Accepts a “Comma-Separated List”. Therefore, the Value of the “@TABLE_TYPE” Parameter must be “Enclosed” in “Double Quotes”, and, “Each” of the “List Items” should be “Enclosed” in “Single Quotes”. In this case, since there is only “One List Item”, it is “Enclosed” in both the “Single Quote” and the “Double Quote”.

C. Using “INFORMATION_SCHEMA.TABLES” System Catalog View - Querying the “INFORMATION_SCHEMA.TABLES” System Catalog View returns a Row for “Each User-Defined Table”, or, “Each User-Defined View” in the “Current Database” in “Use” for which the “Current User” has the “Permissions”.

This System Catalog View is “Similar” to the “SYS.TABLES” System Catalog View, but it returns “Less Columns”.

It is also possible to “Verify” if a “Given Table” “Exists” Inside a Particular Schema.

D. Using the “OBJECT_ID ()” Function - The “OBJECT_ID ()” Function is used to check the “Existence” of a “Table”. If the “Given Table” Exists, it returns a “Non-NULL Value”. If the “Given Table” Exists, it returns a “NULL”.

The “OBJECT_ID ()” Function takes two parameters. The “First Parameter” is the “Table Name” to look for, and, the “Second Parameter” is the “Type of the Object” to look for, e.g., “U” stands for “User Defined Table”.

The “First Parameter” can also be provided as a “Three-Part Name” to Include the “Database”, and, the “Schema” along with the “Given Table Name”.

E. Using “SYS.OBJECTS” System Catalog View - Querying the “SYS.OBJECTS” System Catalog View returns a Row for “Each User-Defined”, “Schema-Scoped Object” in the “Database”.

This System Catalog View does not return only “Tables”, it returns “All Sorts” of “Objects”. So, it is a good idea to narrow the Result down just to “Tables”. Using the Condition “type = ‘U’”, the Information of All the “User-Defined Tables” can be fetched.

It is also possible to “Verify” if a “Given Table” “Exists” Inside a Particular Schema. In that case, if the “SCHEMA_ID” is known that can be provided in the WHERE clause. Otherwise, since the “SYS.OBJECTS” System Catalog View only returns the “Schema ID”, that Column can be Passed to the “SCHEMA_NAME ()” Function in order to get the Name.

Verify If a Table Exists and Create One If It Doesn’t

In “SQL Server”, the “IF NOT EXISTS” Clause can’t be used with the “CREATE TABLE” Statement. Therefore, to “Verify” if a “Given Table” “Exists” inside a “Particular Database”, and, if it “Doesn’t Exist”, “Only” then to “Create” the “Given Table” in “SQL Server”, the following “Four Approaches” are used -

A. Using “SYS.TABLES” System Catalog View - By Querying the “SYS.TABLES” System Catalog View, it is possible to “Verify” if a “Given Table” “Exists”, and, if it “Doesn’t Exist”, “Only” then “Create” the “Given Table”.

Also, by Querying the “SYS.TABLES” System Catalog View, it is possible to “Verify” if a “Given Table” “Exists” inside a “Particular Schema”, and, if it “Doesn’t Exist”, “Only” then “Create” the “Given Table”.

B. Using “INFORMATION_SCHEMA.TABLES” System Catalog View - By Querying the “INFORMATION_SCHEMA.TABLES” System Catalog View, it is possible to “Verify” if a “Given Table” “Exists”, and, if it “Doesn’t Exist”, “Only” then “Create” the “Given Table”.

Also, by Querying the “INFORMATION_SCHEMA.TABLES” System Catalog View, it is possible to “Verify” if a “Given Table” “Exists” inside a “Particular Schema”, and, if it “Doesn’t Exist”, “Only” then “Create” the “Given Table”.

C. Using the “Object_ID ()” Function - By using the “OBJECT_ID ()” Function, it is possible to “Verify” if a “Given Table” “Exists”, and, if it “Doesn’t Exist”, “Only” then “Create” the “Given Table”.

Also, by using the “OBJECT_ID ()” Function, it is possible to “Verify” if a “Given Table” “Exists” inside a “Particular Database” and a “Particular Schema”, and, if it “Doesn’t Exist”, “Only” then “Create” the “Given Table”.

D. Using “SYS.OBJECTS” System Catalog View - By Querying the “SYS.OBJECTS” System Catalog View, it is possible to “Verify” if a “Given Table” “Exists”, and, if it “Doesn’t Exist”, “Only” then “Create” the “Given Table”.

Also, by Querying the “SYS.OBJECTS” System Catalog View, it is possible to “Verify” if a “Given Table” “Exists” inside a “Particular Schema”, and, if it “Doesn’t Exist”, “Only” then “Create” the “Given Table”.

Display Existing Tables

In “SQL Server”, to “Display” the “List” of the “Already Existing Tables” inside the “Database” that is “Currently in Use”, the following approach is used -

Display the Structure or Schema of a Table

In “SQL Server”, there is “No Command” to “Display” the “Table Structure” like Oracle or MySQL provides “DESCRIBE” or “DESC” Command.

The “User” who is going to “Display” the “Structure” or “Schema” of a “Table”, must either be the “Owner” of that “Table”, or, must have been “Granted Permissions” to that “Table”.

In “SQL Server”, to “Display” the “Structure”, or “Schema” of a “Table”, the following “Two Approaches” are used -

A. Using “SP_COLUMNS” System Stored Procedure - The simplest way to “Display” the “Columns” and the “Related Information” of a “Table”, i.e., the “Structure” or “Schema” of a “Table” is to “Use” the “SP_COLUMNS” System Stored Procedure.

This System Stored Procedure returns a “Row” for “Each Column” in the “Table” that “Shows” the “Column Information” in “Detail”.

B. Using “INFORMATION_SCHEMA.COLUMNS” System Catalog View - Querying the “INFORMATION_SCHEMA.COLUMNS” System Catalog View returns a Row for “Each Column” of “Each Table” in the “Current Database”, for which the “Current User” has the “Permissions”.

To “Display” the “Structure”, or “Schema” of a Particular “Table”, the following approach is used -

C. Using “SQL Server Management Studio” (SSMS) - Using the “SSMS”, the following “Steps” are used to “Display” the “Structure”, or “Schema” of a Particular “Table” -

  • Step 1 - Connect to the “Database” and “Navigate” to the “Object Explorer”.
  • Step 2 - Select the “Table” for which to “Display” the “Structure”, or “Schema”.
  • Step 3 - Perform a “Right-Click” on the “Table” that “Displays” and from the Various Options, “Select” the Option “Design” from the “Context Menu”.

The “Structure”, or “Schema” of that Particular “Table” is “Displayed”.

Drop a Table

To “Drop”, or “Delete” an “Already Existing Table” inside a “Database”, the “DROP TABLE <table-name>” Command is used.

Verify If a Table Exists and Drop

In “SQL Server”, the “IF EXISTS” Clause can’t be used with the “DROP TABLE” Statement. Therefore, to “Verify” if a “Given Table” “Exists” inside a “Particular Database”, and, if it “Exists”, “Only” then to “Drop” the “Given Table” in “SQL Server”, the following “Four Approaches” are used –

A. Using “SYS.TABLES” System Catalog View - By Querying the “SYS.TABLES” System Catalog View, it is possible to “Verify” if a “Given Table” “Exists” inside a “Particular Schema”, and, if it “Exists”, “Only” then “Drop” the “Given Table”.

B. Using “INFORMATION_SCHEMA.TABLES” System Catalog View - By Querying the “INFORMATION_SCHEMA.TABLES” System Catalog View, it is possible to “Verify” if a “Given Table” “Exists” inside a “Particular Schema”, and, if it “Exists”, “Only” then “Drop” the “Given Table”.

C. Using the “Object_ID ()” Function - by using the “OBJECT_ID ()” Function, it is possible to “Verify” if a “Given Table” “Exists” inside a “Particular Database” and a “Particular Schema”, and, if it “Exists”, “Only” then “Drop” the “Given Table”.

D. Using “SYS.OBJECTS” System Catalog View - By Querying the “SYS.OBJECTS” System Catalog View, it is possible to “Verify” if a “Given Table” “Exists” inside a “Particular Schema”, and, if it “Exists”, “Only” then “Drop” the “Given Table”.

Display the Metadata of a Table

In “SQL Server”, to “Display” the “Metadata” of a “Table”, i.e., the “Creation Time” of a “Table”, “Column Names”, “Data Types”, “Length”, and the “Constraints” on the “Columns” etc., the System Stored Procedure “SP_HELP” is used.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Oindrila Chakraborty

I have 10+ experience in IT industry. I love to learn about the data and work with data. I am happy to share my knowledge with all. Hope this will be of help.