How to view table in SQL Server

In this SQL Server tutorial, we will understand how to view table in SQL Server. Additionally, we will cover the following set of topics.

• How to view table in SQL Server
• How to view table data in SQL Server Management Studio
• How to view table definition in SQL Server using Transact-SQL
• How to view table definition in SQL Server Management Studio
• How to view table structure in SQL Server
• How to view table definition in SQL Server
• How to view inserted table in SQL Server
• How to view table relationships in SQL Server
• How to view table relationships in SQL Server using Transact-SQL
• How to view table relationships in SQL Server Management Studio
• How to view table schema in SQL Server
• How to view table schema in SQL Server Management Studio
• How to view database table in SQL Server Management Studio

How to view table in SQL Server

In SQL Server, we can easily view a table using a SELECT statement. And the main usage of the SELECT statement is to fetch data from database objects. However, a table in one just database object whose data can be fetched using SELECT statement.

Let’s look at the syntax of using a SELECT statement in SQL Server.

--To fetch specific columns
SELECT column1, column2, ...
FROM table_name;

--To fetch all the columns
SELECT * FROM table_name

In the syntax, first, we need to use the SELECT keyword. After this, we can either specify some specific column names from a table or use * to fetch all columns. In the end, we have to use FROM table_name statement where table_name is the name of the table.

Now, let’s take a look at an example in SQL Server.

USE [sqlserverguides]
GO

SELECT * FROM dbo.Customers
GO

The above query will return all the data from the Customers table.

Read How to create a table in sql server management studio

How to view table definition in SQL Server

In this section, we will illustrate how to view the definition of a table in SQL Server. Now, a table in SQL Server is also a database object that holds data stored in a database. Moreover, tables are similar to spreadsheets in that data is logically structured in a row-and-column manner.

In SQL Server, there are commonly two ways to view table definition. The first method is by using SQL Server Management Studio, and the second way is by executing Transact-SQL queries. Let’s discuss both methods in more detail.

Read Create a table from view in SQL Server

How to view table definition in SQL Server Management Studio

To fetch the definition of a table, we need to follow the following steps in SQL Server Management Studio.

• First, run SQL Server Management Studio and connect to the required database instance.
• Next, from the Object Explorer, first, expand the Database instance and then expand the Databases directory.
• After this, right-click the required database and then navigate to Tasks option and select the Generate Script option. This will open a new Generate Script window.

• In the new window, click on the Next button to navigate to the new page.

• Next, select the “Select specific database objects” option. And then, from the given list expand the Tables directory and tick mark the required table, and click on Next.

• After this, we need to select the option for how a script should be saved. In our case, we have selected the “Open in a new query window” option.

• In the end, click on the Next button, and it will open the script for the selected table in the query editor.

Read How to export data from SQL Server to Excel

How to view table definition in SQL Server using Transact-SQL

In SQL Server, there are multiple queries that we can use to see the definition of a table. However, in this section, we will illustrate one well-known method to fetch a table definition.

In SQL Server, sp_helptext is a system stored procedure that displays the definition of different objects. Moreover, these objects also include tables, views, triggers, etc. And this procedure can easily return the definition of a given table.

Now, let’s understand how to use this procedure to get the definition of a table in SQL Server. And we will use the following query.

USE [sqlserverguides]        --sqlserverguides is the database name
GO

EXEC sp_help 'dbo.Customers' --dbo.Custoemers is the table name

In the above example, we are using the sp_help procedure to get the table definition of the Customers table. Now, this table carries data of customers from different countries like the United States, Canada, etc.

Moreover, this table is located in the sqlserverguides database. Therefore, when we execute the above query, we will get the following result.

Read Advanced Stored Procedure Examples in SQL Server (17 Examples)

How to view table relationships in SQL Server

In SQL Server, multiple tables are linked together using primary and foreign keys. And this link between tables can be stated as a relationship between them. However, to view the relationship between tables, we need to view the foreign key attributes.

Now, we can view table relationships in SQL Server either using SQL Server Management Studio or by Transact-SQL. Let’s discuss both methods in detail.

How to view table relationships in SQL Server Management Studio

Now, in SQL Server Management Studio, there are two ways to view a relationship between the tables. The first method is to view the Foreign Key attributes, and the second method is to create a database diagram that displays the relationship.

#1: Create a Database Diagram

• First, run SQL Server Management Studio and connect to the required database instance.
• Next, from the Object Explorer, first, expand the Database instance and then expand the Databases directory.
• After this, right-click the Database Diagrams directory and click on New Database Diagram option. This will open an Add Table dialog box.

• From the Add Table dialog box, select the required table and click on Add button.

• After adding all the required tables, close the dialog box. In the end, SQL Server will display the database diagram showing the relation between the tables.

#2: View the foreign key attributes

• From the Object Explorer, first, expand the Database instance and then expand the Databases directory.
• After this, expand the required database from the available databases.
• Next, expand the Tables directory and right-click the required table, and click on the Design option. Therefore, this will open a Table Designer page.

• On the Designer page, right-click and select the Relationships option, and this will open a Foreign Key Relationships dialog box.
• From the dialog box, we can easily view the foreign key relations and its properties.

Read SQL Server Substring Function [9 Examples]

How to view table relationships in SQL Server using Transact-SQL

Now, by using Transact-SQL, we cannot direct virtualize the table relationship directly. For this task, we can list the foreign key attributes that depict the relationship between the table.

Therefore, to get the foreign key attributes, we will use two system catalog views in SQL Server. The first is sys.foreign_keys catalog view and the second is the sys.foreign_key_columns view. Let’s look at an example for a better understanding.

USE [sqlserverguides];  
GO  
SELECT   
    fk.name AS [Foreign Key Name]  
   ,OBJECT_NAME(fk.parent_object_id) AS [Table Name]  
   ,COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS [Constraint Column Name]  
   ,OBJECT_NAME (fk.referenced_object_id) AS [Referenced Table]  
   ,COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS [Referenced Column]    
FROM sys.foreign_keys AS fk  
INNER JOIN sys.foreign_key_columns AS fkc   
   ON fk.object_id = fkc.constraint_object_id   
WHERE fk.parent_object_id = OBJECT_ID('dbo.Marks'); 

In the above example, we have given a query to fetch the foreign key relationship details for the Marks table. And this table is available in the sqlserverguides database.

Therefore, after execution, SQL Server will return a result similar to the result shown below.

Read SQL Server Port

How to view table schema in SQL Server

In SQL Server, a schema is a set of the logical structure of data. And tables, views, triggers, stored procedures, indexes, and other database objects are all part of a schema.

Moreover, the owner of these logically related database objects is associated with a username known as the schema owner.

Now, how we can get to know the schema of a table in SQL Server. For this task, there are multiple options, first, we can use SQL Server Management Studio, or we can execute a query.

How to view table schema in SQL Server Management Studio

SQL Server Management Studio is a very efficient graphical user interface tool for SQL Server. And it helps in making all the database-related tasks easy. Moreover, we can also use it to view the schema of a table.

For this task, we just need to expand the tables directory under our required database. All the table names carry the schema names as well.

For example, if the table name is dbo.UnitedStates then, dbo is the schema name and UnitedStates is the actual table name.

Alternatively, we can view the properties of a table to view the table schema. For this task, right-click the table and select the Properties option. Therefore, the properties dialog box will also display the schema name separately.

Read SQL Server Convert Function

How to view table schema in SQL Server using query

To get the schema name in SQL Server, we can query the system information schema view. This view is named INFORMATION_SCHEMA, and it stores information related to tables and views in the current database.

Let’s understand this execution using an example in SQL Server.

USE [sqlserverguides]
GO

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Students'

In the above example, we are querying the INFORMATION_SCHEMA.TABLES to fetch details related to a table whose name is Students. Hence, this query will return details including the schema of the table.

Read SQL Server Convert Datetime to date

How to view table structure in SQL Server

In SQL Server, we can easily view the structure of a table either using SQL Server Management Studio or by executing a query. Therefore, we will illustrate both methods with an example.

Using SQL Server Management Studio

• First, run SQL Server Management Studio and connect to the required database instance.
• Next, from the Object Explorer, first, expand the Database instance and then expand the Databases directory.
• After this, expand the required database and then, expand the Tables directory.
• From the list of tables, right-click the required table and click on Design option.

• This will open a table designer page where we can easily view the table structure as well as its properties.

Using Transact-SQL

Alternatively, there are multiple options in SQL Server, using which we can fetch table structure details. For example, sp_columns is a system stored procedure in SQL Server that returns column details of a specified table.

Now, let’s use this procedure to execute an example in SQL Server. Therefore, the example is shown below.

USE [sqlserverguides]
GO

EXEC sp_columns 'Students'
GO

In the above example, we are using the sp_columns to get the column details of the Students table which is located in the sqlserverguides database. Hence, after execution, we will get the following result.

Read How to create functions in SQL Server Management Studio

How to view table data in SQL Server Management Studio

In this section, we will understand how to view the data of a table in SQL Server Management Studio. Now, to implement this task, we can follow the following steps in SQL Server Management Studio.

• From the Object Explorer, first, expand the Database instance and then expand the Databases directory.
• After this, expand the required database from the list of databases and then, expand Tables directory.
• Now, from the list of tables, right-click the required table and then click on SELECT TOP 1000 ROWS option.

• After this, it will automatically run a query and list the first 1000 rows from the selected table.

Read SQL Server Add Column

How to view inserted table in SQL Server

SQL Server also facilitates the use of inserted and deleted tables. These tables are mainly utilized in DML trigger statements to define conditions for trigger actions and to test the impacts of various data changes. Moreover, these temporary tables are created and managed automatically by SQL Server.

Now, these tables get affected in the following way when various DML actions take place in the trigger.

• When an entry gets inserted into a primary table, the INSERTED table receives a new entry for that record.
• Similarly, when an entry gets deleted from a primary table, the DELETED table receives a new entry for that record.
• However, if an entry in the primary table is updated, the old entry is added to the DELETED table, and the new entry is inserted into the INSERTED table.

Now, let’s understand the whole implementation related to the INSERTED table using an example. For this, consider the following SQL code.

--Creating a table
USE [sqlserverguides]
GO

CREATE TABLE [dbo].[Customers_US](
	[id] [int] NULL,
	[customer_name] [varchar](50) NULL,
	[city] [varchar](50) NULL
) ON [PRIMARY]
GO

In the above query, we have created a table whose name is Customers_US. Next, we will create an INSERTED table using a trigger in SQL Server.

USE [sqlserverguides]
GO
 
CREATE TRIGGER trInsertCustomers 
ON Customers_US
FOR INSERT
AS
BEGIN
  SELECT * FROM INSERTED
END

In the above example, we have created an INSERT trigger. So, now, whenever we insert a record in the Customers_US table then, the trigger will return the data of the INSERTED table. Now, let’s insert a record in the Customers_US table using the INSERT statement.

USE [sqlserverguides]
GO

INSERT INTO [dbo].[Customers_US]
           ([id]
           ,[customer_name]
           ,[city])
     VALUES (101, 'Allin Mazin', 'New York')
GO

Now, whenever, we insert a new record in the Customers_US table, it will return the INSERTED table.

You may like the following SQL server tutorials:

• SQL Server Agent won’t start
• SQL Server User Permissions
• Indexed views in SQL Server
• MySQL vs SQL Server – Key Differences
• SQL Server drop table if exists
• SQL Server view order by
• Exception Handling in SQL Server
• View SQL Server Error Logs

So, in this tutorial, we have understood how to view table in SQL Server. Additionally, we have covered the following set of topics.

• How to view table in SQL Server
• How to view table data in SQL Server Management Studio
• How to view table definition in SQL Server using Transact-SQL
• How to view table definition in SQL Server Management Studio
• How to view table structure in SQL Server
• How to view table definition in SQL Server
• How to view inserted table in SQL Server
• How to view table relationships in SQL Server
• How to view table relationships in SQL Server using Transact-SQL
• How to view table relationships in SQL Server Management Studio
• How to view table schema in SQL Server
• How to view table schema in SQL Server Management Studio
• How to view database table in SQL Server Management Studio