What is Table?
Tables are database objects
that contain all the data in a database. In tables, data is logically organized
in a row-and-column format similar to a spreadsheet. Each row represents a
unique record, and each column represents a field in the record.
There are different types of table
available in SQL Server
1. Partitioned Tables
2. Temporal tables
3. Wide Tables
4. System Tables
Please
refer the below link to more about the Tables.
https://learn.microsoft.com/en-us/sql/relational-databases/tables/tables?view=sql-server-ver16
Create a
table in SQL Server
There are multiple ways to create a table in
SQL server.
1. Using Table designer SQL server
Management Studio(SSMS)
2. Using T SQL
In this
blog we are going to see about, creating table using the Transaction SQL.
Please
find the generic basic syntax on create table using the SQL.
CREATE TABLE Table_Name(
Column_Name1 DataType1,
Colun_Name2 DataType2,
Column_Name3 DataType3)
CREATE
and TABLE are the keywords used to create the table with proceeding with table
name
After the table name we need to mention the
column names to be stored in the table name.
We will create the sample table for storing the
student details.
CREATE TABLE Student(
StudentRollNo INT,
StudentName VARCHAR(70),
Gender CHAR(1),
StudentDOB DATE
)
We can use the same way to delete the table in
SQL server.
We can use the SSMS Object Explorer -> Right
Click the Table and click the drop.
We can Use T SQL DROP command to delete the
table
Syntax
DROP TABLE TableName
We can use the below code to delete the table
using the above command.
DROP TABLE Student
Notes : There are set of Limitations and
restrictions apply while delete the table in SQL server. Please refer the below
link on the same.
Rename
the Table in SQL Server
We can use the same two way to rename the table
in SQL server.
We can use the SSMS Object Explorer -> Right
Click the Table and click the rename.
We can use pre defined Stored Procedure to
rename the table name
Syntax
EXEC sp_rename
‘Table Name to be renamed along with Schema Name’, 'New table name';
We can use the below code to rename the table
using the below.
EXEC sp_rename 'dbo.Student', 'StudentDetils'
Notes : Please refer the below link for more
info
View the
existing Table Definition
We will often encounter the problem to view the
table definition details while look into the existing SQL server DB. For that
we can use the below.
1. We can use the existing procedure
sp_help to know about the table definition
Ex : EXEC
sp_help 'dbo.student;
2. Using the system tables we can view
the table definition
SELECT s.name as schema_name, t.name as table_name, c.* FROM
sys.columns AS c INNER JOIN sys.tables AS t ON t.object_id = c.object_id INNER
JOIN sys.schemas AS s ON s.schema_id = t.schema_id WHERE
t.name = 'mytable' AND s.name = 'dbo';
3. Using the SSMS object explorer
design option
Refer the
below link to know more about this
View the
dependencies of the Table in SQL server
We will
often encounter the problem to view the table dependency details while look
into the existing SQL server DB for modification. For that we can use the below.
1. We can use the existing view to
search about dependency of all SQL Server Objects
USE AdventureWorks2012;
GO
SELECT * FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');
GO
2. Using the SSMS object explorer
design option right click the table and click on view dependencies to vie the
details.
Refer the
below link to know more about this
Comments