Most of the times while working with SQL server ,we are definitely dealing with lots of tables and it is quite straight forward that the data in SQL server are stored in the form of tables .Then it is pretty necessary that we should know the structure of the table that we are working on . The structure of the table gives the details of various columns,data types , length of the column, null-able fields , key dependencies on various columns etc. Here are some of the simplest methods to find the structure of a table.
Note : ‘Classroom’ is the table name that is used in this entire content.
Example table creation in SSMS (Sql Server management Studio) :
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE dbo.[Classroom] ( [ Student_ID ] INT NOT NULL IDENTITY(1,1), [ Name_Of_Student ] VARCHAR(30) NOT NULL, [ Age ] INT NULL, [ Total_Marks ] INT NULL, [ School_Name ] VARCHAR(200) NULL, [ City ] VARCHAR(30) NOT NULL ) |
Method 1 – Alt + F1 keys:
This is one of the easiest shortcuts to find the table structure .To use this , select the table name or just double click on it and press Alt + F1
Once the table name is selected and Alt + F1 is pressed you can see the table structure details in the bottom pane aka Results window.
Method 2 – Information Schema:
Information_Schema views allow you to see various information about the objects in a database. This method is to query the table information by just giving the table name in the ‘where’ clause
1 2 3 4 |
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='Classroom'; |
Method 3 – Sp_Help:
Sp_Help is a system stored procedure to provide detailed information about the object that is passed to it .
1 2 3 |
EXEC SP_HELP 'Classroom'; |
Method 4 – Sp_Columns :
Sp_Columns is same as that of Sp_Help but the only difference is here the columns of the table are given priority.
1 | EXEC SP_COLUMNS 'Classroom' ; |
Method 5 – Object explorer drill down:
This method can be done in SSMS(Sql Server Management Studio) . Most of the times the object explorer will be found to the left of the ssms window . If it is not visible click on View -> Object Explorer (this will be on the top) or press F8 or just type object explorer in the quick launch bar .
Once the object explorer is visible double click on Databases ,then choose the database that has your table . Now click on tables option and now you can see your table in the drop down. Right click on table name and choose ‘Script Table As’ option then ‘Create To’ option . Now you and see the script of the table in the query window.