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 : 1532 | Status : [Member] | Replies : 16
how to see the table design in sql using sql queries




Responses

Posted by: Sutotpal on: 10/13/2011 [Member] Starter | Points: 25

Up
0
Down
hi,

Use this,

EXEC sp_help Table_name


Best Regards,
Sutotpal

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

Posted by: Alex_4youin on: 10/13/2011 [Member] Starter | Points: 25

Up
0
Down
desc <table_name>

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: mareesnectar-12158 on: 10/14/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Alex_4youin
desc <table_name>
its not working

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

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

Up
0
Down
Hi Niladri.biswas

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>'
The above code is used to view database........etc..
DB Name Table Schema Table Name Column Name Data Type Data Type Size Default Value Constaint
Marees dbo emp empid int NULL NULL PK_emp
Marees dbo emp empname varchar 10 NULL PK_emp
Marees dbo emp empsalary varchar 10 NULL PK_emp


but i am asking ... design view .... i want to see design and i want to change datatype...
Thanks

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

Posted by: Oswaldlily on: 10/14/2011 [Member] Starter | Points: 25

Up
0
Down
then better double click on table name..

in queries u can just view ..

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

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

Up
0
Down

Hello..Oswaldlily ...
i know that one..... i want sql quires .....

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

Posted by: Vuyiswamb on: 10/14/2011 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Create this Stored Proc

Create  Proc sp_helptextTable

(
@TableName Nvarchar(2000)
)
as
Create Table #Text
(
Source NVarchar(4000)
)

Insert Into #Text(Source) Values ('Create Table ' + @TableName + '(');

Insert Into #Text(Source)
Select
'[' + C.Name + '] ' + Ty.name + Case When C.Scale Is NULL Then '(' + Cast(C.Length as Varchar) + ') ' Else '' End +
Case When C.IsNullable =0 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End
+ Case When C.Colstat & 1 = 1 Then ' Identity(' + Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') ' Else '' End
+ Isnull(' Constraint ' + ChkCon.Name + ' Check ' + comments.Text ,'')
+ Isnull(' Default ' + defcomments.text ,'') + ','
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'
Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id
And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder
Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1
Left Outer Join sysobjects def On def.parent_obj = T.Id
And def.xtype= 'D' And def.Info = C.Colorder
Left Outer Join syscomments defcomments ON defcomments.id = def.id

Where
T.Type='U'
And T.Name=@TableName
Order By
T.Name,
C.Colorder


Insert Into #Text(Source)
Select
'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@TableName, 2) + '),'
From
sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@TableName)
and ind.name = object_name(tbl.id)
and xtype in ('PK', 'UQ')

Insert Into #Text(Source)
select
'Constraint [' + tbl.name + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +
object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'
from
sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@TableName)
and fk.constid = tbl.id
and xtype in ('F')

Declare @Source as Nvarchar(4000);
Select @Source = Source From #Text;
Update #Text
Set
Source = Substring(Source,1,Len(Source)-1)
Where
Source = @Source;

Insert Into #Text(Source) values (')');

Select Source From #Text


and execute it like this

sp_helptextTable 'Mytablename'


Thank you for posting at Dotnetfunda
[Administrator]

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

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

Up
0
Down
Dear Vuyiswamb

Did you forget you post the function script "dbo.GetAllIndexedColumns" ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: Rickeybglr on: 10/17/2011 [Member] Starter | Points: 25

Up
0
Down
DESC is only for Oracle not sql server

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'


Hope this will help...

let me know any Questionss

Regards
Sriram.R

sriram

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

Posted by: Muhsinathk on: 9/19/2012 [Member] Bronze | Points: 25

Up
0
Down
Please Execute
SP_HELP <TABLE NAME>

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

Posted by: Muhsinathk on: 9/19/2012 [Member] Bronze | Points: 25

Up
0
Down
Please mark as answer if it helpful to you..That helps others who search the same..

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

Posted by: Hariinakoti on: 9/28/2012 [Member] Starter | Points: 25

Up
0
Down
Good Job niladri

Thanks & Regards
Hari

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

Posted by: Jayakumars on: 9/28/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

Type Table Name like this Empname
Select your table name Select Press Alt+F1
you will achieve this.


Mark as Answer if its helpful to you

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

Posted by: Jayakumars on: 9/28/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
Please mark as answer if it helpful to you.


Mark as Answer if its helpful to you

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

Login to post response