How to check table definition in SQL server?

SQL Tech

How to check Table structure in SQL Server?

Naren 0Comment

We can use several methods to check table definition or structure, including SQL Server Management Studio (SSMS) or T-SQL queries.

Method 1: Using SQL Server Management Studio (SSMS)

  • Launch SQL Server Management Studio and connect to your SQL Server instance.
    Expand Databases In the Object Explorer and Find the Table and Expand the Tables node to see the list of tables in the database.View Table Definition:
  • Right-click on the table whose definition you want to check.
    Select Design. This will open the table in the design view where you can see the columns, data types, and other properties.

Method 2: Using T-SQL Queries:

  • EXEC sp_help ‘YourTableName’

There are several other mentod to genrate table script these are listed below.

1.Using INFORMATION_SCHEMA.COLUMNS View:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘test’;

2.Using sys.columns and sys.tables System Catalog Views:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘test’;

3.Using sys.columns and sys.types:

SELECT
c.name AS ColumnName,
t.name AS DataType,
c.max_length AS MaxLength,
c.precision,
c.scale,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID(‘test’);