views:

13

answers:

1

how to get all the tables and structure in a database in a table format using an sql query or stored procedure?

Structure is like below:

Sl No   FieldName   DataType    Size    Description
1      UserName    varchar     50  
+1  A: 

This should do the trick. There is a bit more information in here but I think you may find it useful.

Select  t.Table_Schema,
        t.Table_Name,
        c.Column_Name,
        IsNull(c.Column_Default, '') as 'Column_Default',
        c.Is_Nullable,
        c.Data_Type,
        IsNull(c.Character_Maximum_Length, IsNull(Numeric_Precision,'') + IsNull(Numeric_Scale, IsNull(DateTime_Precision,''))) as 'Size'

From Information_Schema.Tables t

Join Information_Schema.Columns c on    t.Table_Catalog = c.Table_Catalog
                                And     t.Table_Schema = c.Table_Schema
                                And     t.Table_Name = c.Table_Name

Where t.Table_Type = 'BASE TABLE'

Order by t.Table_Schema, t.Table_Name, c.Ordinal_Position
Barry
+1 But you left off the "and structure", whatever that is. Constraints? Indexes? Functions? Stored Procedures? Views?, etc. ;-)
Brock Adams
@Brock Adams - yes this is true. "and structure" is slightly vague so I kinda based it on the expected output. Having said that I'm not sure where description comes in to it either :-)
Barry

related questions