How do I: Print a table structure?

Question

Hello: Is there somewhere in Visual Studio 2008... where I can print the structure of a table? Or is there a tool that is free or cheap that I can download?

Sam Hobbs · Answer

The attached should show you the general idea of getting schema information. In the project, you will need to alter the connection string in the settings, and also alter the table name in the select.

Sam Hobbs · Answer

I am sure there are many ways. I know there are many ways to read a table's schema and then you could format it the way you want to. One way is to go to the Server Explorer, then the database, then the table and right-click on the table; select "Open Table Definition". Then click in the upper left corner; the area at the left of the headers, so that all fields and all columns are selected. Then just copy to the clipboard and paste in a text editor, such as the VS IDE. That won't show all of the table schema data but it will show field names and field types. I think Access has some tools that would help and I assume SQL Server does too, but I currently have SQL Server Express insalled and it seems to be missing a lot that the full SQL Server has.

Gustavo · Answer

Sam: Yea, I did a query on it and saw a lot of more fields. So for now I changed the SELECT to SELECT *.

Sam Hobbs · Answer

Note that there are many other fields that could be obtained, such as the maximum length of character fileds. You can use the query designer to explore them.

Gustavo · Answer

Sam: Now I just have to get my PrintDataGridView routine to work and I will be done with this part.

Gustavo · Answer

Sam: YES... It worked. This is the code I used: DBCommand = "SELECT" + " COLUMN_NAME" + " , DATA_TYPE" + " , NUMERIC_PRECISION" + " FROM INFORMATION_SCHEMA.COLUMNS" + " WHERE (TABLE_NAME = 'Field')" ; ClassDB ClassDB = new ClassDB (DBConnectionString); ClassDB.LoadDataGridView(DBCommand, dataGridViewSchema); Thank you very much AGAIN.... LOL

Gustavo · Answer

Sam: Yea.. .that is getting there. I have to now implement it into my code. The main items was the 'SELECT' of the schema. After I have your version working, I will just send the 'SELECT' to my LoadDataGrid routine. Will let you know when I get it working.

Sam Hobbs · Answer

Also in Server Explorer, right-click on your database and choose "Publish to provider". Play with that. It seems to create a stored procedure to do the type of thing you are looking for.

Gustavo · Answer

Sam: I have already done that. That is the look of what I am looking for. However: I would like to go it within my program, or some routine thatI select the table and then click print. I know I am getting lazy.

Sam Hobbs · Answer

Go to the Server Explorer in Visual Studio. Open the node for your database. Right-click on "Database Diagrams". It might ask you if you want to create a couple of things, but then you can create a diagram. Choose the table from the list box. Then in the diagram. right-click on the header of the diagram and then select "Table View". I think that will get you close to what you are asking; at least it is what VS offers. If you look in the SQL Server documentation, there are stored procedures and/or views for getting table information.

Gustavo · Answer

Sam: I did not think about pastgin it to Excel. However: I want to try to get or write a routine that I can just select the table, click button and have it print. I found some routines, but they want $$$ for it. I found one written in VB... I might try to convert it to c#. I found some in MSDN at: http://msdn.microsoft.com/en-us/library/aa287528(v=VS.71).aspx , but I cant get it to work. It looks very simple: private void printGrid_Click(System.Object sender, System.EventArgs e) { printDocument1.Print(); } private void printDocument1_PrintPage(System.Object sender, System.Drawing.Printing.PrintPageEventArgs e) { PaintEventArgs myPaintArgs = new PaintEventArgs(e.Graphics, new Rectangle(new Point(0, 0), this.Size)); this.InvokePaint(dataGrid1, myPaintArgs); }

Sam Hobbs · Answer

Yes, pasting into a text file is not useful. I think that with a little bit of experience with other tools, you can format the data. Have you ever used Excel? Pasting into Excel is an easy way to format the data. Have you ever used Access? Pasting into Access is an easy way to format the data, except you might need to create an empty table with three columns. If you write a program that reads the schema, then one possibility is to write the data as a tab-delimted file and then use Access or Excel to format the data.

Gustavo · Answer

Sam: I tried that. 1) It does not give a copy option. 2) I did a ctrl+c and then pasted... Its a horrible paste. I have looked for programs/tools that would do it for me, but they are 149$ and they are not so good. I have found some code that will allow me to extract the schema and put it in a datagrid. I might do that and then write it to a work table and then dump it with the Report Writer (maybe). I was using the snipping tool, but tpo do it to so many tables, its a pain. I looked at Access 2007 and can not find it. I thought it did, but I forget where/how. SQL Server, might but thet same... cant find where/how.