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.

How to view table in SQL Server
How to view table in SQL Server

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.
How to see view definition in SQL Server Management Studio
Generating Script in SSMS
  • In the new window, click on the Next button to navigate to the new page.
get view definition in SSMS
Generating Script in SSMS
  • 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.
How to view table definition in SQL Server Management Studio
How to view table definition in SQL Server Management Studio
  • 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.
view definition in SSMS
Selecting how a script should be saved
  • 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.

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

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.
How to view table relationships in SQL Server Management Studio
Create New Database Diagram
  • From the Add Table dialog box, select the required table and click on Add button.
How to view table relationships in SSMS
Adding tables to Database Diagram
  • 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.
creating relational diagram in SSMS
Relational Diagram Example in SSMS

#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.
View table relationships in SSMS
View table relationships in SSMS
  • 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.
View the foreign key attributes in SSMS
View the foreign key attributes in SSMS

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.

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

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.

How to view table schema in SQL Server Management Studio
How to view table schema in SQL Server Management Studio

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.

How to view table schema in SSMS
How to view table schema in SSMS

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.

How to view table schema in SQL Server
How to view table schema in SQL Server

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.
How to view table structure in SSMS
Design option in SSMS
  • This will open a table designer page where we can easily view the table structure as well as its properties.
View table structure in SSMS
View table structure in SSMS

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.

How to view table structure in SQL Server
How to view table structure in SQL Server

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.
How to view table data in SSMS
How to view table data in SSMS
  • After this, it will automatically run a query and list the first 1000 rows from the selected table.
View table data in SSMS
How to view database table in SQL Server Management Studio

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.

How to view inserted table in SQL Server
How to view inserted table in SQL Server

You may like the following SQL server tutorials:

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