how to see the table design in sql using sql queries

Posted by mareesnectar-12158 under Sql Server on 10/13/2011 | Points: 10 | Views : 29761 | Status : [Member] | Replies : 4
how to see the table design in sql using sql queries




Responses

Posted by: PandianS on: 10/13/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

You can use Sp_Help system stored procedure
Sp_Help Table1

Go



Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: mareesnectar-12158 on: 10/13/2011 [Member] Starter | Points: 25

Up
0
Down
Sp_Help Table1

Go

The above code is used to show Table view ...
so give me the code to show design view

mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.biswas on: 10/13/2011 [Member] Platinum | Points: 25

Up
0
Down
If you want to look for a single table use

EXEC SP_HELP <Table_Name>


OR


SELECT
[DB Name] = isc.TABLE_CATALOG
,[Table Schema] = isc.TABLE_SCHEMA
,[Table Name] = isc.TABLE_NAME
,[Column Name] = isc.COLUMN_NAME
,[Data Type] = DATA_TYPE
,[Data Type Size] = [CHARACTER_MAXIMUM_LENGTH]
,[Default Value] = COLUMN_DEFAULT
,[Constaint] = isccu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.COLUMNS isc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE isccu
ON isc.TABLE_NAME = isccu.TABLE_NAME
WHERE isc.TABLE_NAME = '<Table Name>'


For All tables, you can use


SELECT
[DB Name] = isc.TABLE_CATALOG
,[Table Schema] = isc.TABLE_SCHEMA
,[Table Name] = isc.TABLE_NAME
,[Column Name] = isc.COLUMN_NAME
,[Data Type] = DATA_TYPE
,[Data Type Size] = [CHARACTER_MAXIMUM_LENGTH]
,[Default Value] = COLUMN_DEFAULT
,[Constaint] = isccu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.COLUMNS isc
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE isccu
ON isc.TABLE_NAME = isccu.TABLE_NAME
WHERE isc.TABLE_NAME IN (SELECT NAME FROM SYS.TABLES WHERE TYPE='U')


Best Regards,
Niladri Biswas

mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 10/27/2011 [Member] Starter | Points: 25

Up
0
Down
Exec sp_help 'Employee'

regards
Sriram.R

sriram

mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response