dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: sqlserver database table structures

  1. #1
    Join Date
    Mar 2002
    Location
    India
    Posts
    49

    sqlserver database table structures

    Hi,
    I would like to know if there is in any database documentation facility in sqlserver or thru vb coding. That is i want to get the structure of the database tables (similar to the desc tablename in oracle) into a text file. Is this Possible ?
    Thanks In Advance

  2. #2
    Join Date
    Apr 2000
    Location
    Southampton, UK
    Posts
    329
    Certainly in SQL Server itself you can Generate SQL Scripts which will contain the statements necessary to drop/create the table(s), view(s), Stored Procedure(s) etc along with any appropriate index creation. The methods for accessing this depend in part on the version of SQL server that you are using, however with SQL 7 and 2000 it is generally a case of right-clicking the object and choose all tasks from the context menu and then choose generate scripts. You can then set the appropriate properties to generate the script.

    From VB it is possible to achieve the same results by using the SQL-DMO object libraries, however this requires a reasonable amount of effort. You can also obtain table definitions and view definitions using the .OpenSchema method of the ADODB.Connection object.

    Take your pick really, generating scripts from SQL Server itself is probably the easiest place to start.
    TimCottee
    I know a little about a lot of things and a lot about very little.

    Brainbench MVP For Visual Basic
    http://www.brainbench.com

    MCP, MCSD, MCDBA, CPIM

  3. #3
    Join Date
    Jul 2002
    Location
    Islamabad, Pakistan
    Posts
    35
    Why dont u try writing a small Visual Basic application to do the task. Use ADOX.Catalog object to open the database and u will get all the table lists, field lists and indexes associated with them in one go.

    Similar is true using simple ado. Simply open a recordset and loop over the rs.fields. the Rs.fields.type will give you all the stuff u need.


    Ali

  4. #4
    Join Date
    Jul 2002
    Location
    Islamabad, Pakistan
    Posts
    35
    Why dont u try writing a small Visual Basic application to do the task. Use ADOX.Catalog object to open the database and u will get all the table lists, field lists and indexes associated with them in one go.

    Similar is true using simple ado. Simply open a recordset and loop over the rs.fields. the Rs.fields.type will give you all the stuff u need.

    I haven't tried this on sql server though, so spare me if it does not work.

    Ali

  5. #5
    Join Date
    Apr 2000
    Location
    Southampton, UK
    Posts
    329
    ADOX is the other solution that I forgot to include in the list of options, I can confirm that you can use this against SQL Server and you can then get slightly more information than you might be able to with standard ADO. In the long run though, especially if you have defaults and field descriptions associated with the table's fields in SQL 2000 you will not get this from ADO or ADOX and will need to use either SQL-DMO (still not guaranteed) or native SQL.
    TimCottee
    I know a little about a lot of things and a lot about very little.

    Brainbench MVP For Visual Basic
    http://www.brainbench.com

    MCP, MCSD, MCDBA, CPIM

  6. #6
    Join Date
    Mar 2002
    Location
    India
    Posts
    49

    THANKS

    Hi
    I have used generate sql script and done the job
    Thanks for the reply
    Bye

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)