Printing Database Structure

I did this once before, but forgot how I did it, and can't figure out how I found the answer either.   Do any of you know how I can print all the table names, columns in the tables, and data types of the columns from within MS SQL Server Mgmnt Studio? Thanks. ~Steve http://lanctr.com/

>Do any of you know how I can print all the table names, columns in the > tables, and data types of the columns from within MS SQL Server Mgmnt > Studio? Could creating a diagram with all the tables on it help here? This can show all tables, column data and relationships. Diagrams can be printed.. Dominic ----- Excess quoted text cut - see Original Post for more -----

Diagrams can do this, but I don't have the time to sit down and arrange all the little boxes so that they fit on pages.  I was able, some time ago, to make a list of my tables with their columns and the column's data types through a query (I think).  Afterwards I was able to copy and paste it into excel for printing.  Again, I THINK I did it this way, but who knows, I've slept since then. ~Steve http://lanctr.com/ >Do any of you know how I can print all the table names, columns in the > tables, and data types of the columns from within MS SQL Server Mgmnt > Studio? Could creating a diagram with all the tables on it help here? This can show all tables, column data and relationships. Diagrams can be printed.. Dominic > I did this once before, but forgot how I did it, and can't figure out how I ----- Excess quoted text cut - see Original Post for more -----

ER/Studio auto-arranges tables quite well.  But, you pay a pretty price for it. m!ke Diagrams can do this, but I don't have the time to sit down and arrange all the little boxes so that they fit on pages.  I was able, some time ago, to make a list of my tables with their columns and the column's data types through a query (I think).  Afterwards I was able to copy and paste it into excel for printing.  Again, I THINK I did it this way, but who knows, I've slept since then. ~Steve http://lanctr.com/

Do you can try this statement: SELECT TABLE_CATALOG,        TABLE_NAME,COLUMN_NAME,        DATA_TYPE,        CHARACTER_MAXIMUM_LENGTH,        NUMERIC_PRECISION,        NUMERIC_SCALE,        IS_NULLABLE,        COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IS_AUTOINCREMENT,        COLUMN_DEFAULT FROM   INFORMATION_SCHEMA.COLUMNS ORDER BY 2,3 Cheers Marco Antonio On Fri, Apr 25, 2008 at 5:43 PM, Steve Good <sgood@lanctr.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

AHA! This looks familiar...  I'll give it a whirl.  Thanks! ~Steve http://lanctr.com/ Do you can try this statement: SELECT TABLE_CATALOG,        TABLE_NAME,COLUMN_NAME,        DATA_TYPE,        CHARACTER_MAXIMUM_LENGTH,        NUMERIC_PRECISION,        NUMERIC_SCALE,        IS_NULLABLE,        COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IS_AUTOINCREMENT,        COLUMN_DEFAULT FROM   INFORMATION_SCHEMA.COLUMNS ORDER BY 2,3 Cheers Marco Antonio On Fri, Apr 25, 2008 at 5:43 PM, Steve Good <sgood@lanctr.com> wrote: > I did this once before, but forgot how I did it, and can't figure out how I ----- Excess quoted text cut - see Original Post for more -----

Yep, that did the trick.  You rock! This time I saved the query ;) ~Steve http://lanctr.com/ Do you can try this statement: SELECT TABLE_CATALOG,        TABLE_NAME,COLUMN_NAME,        DATA_TYPE,        CHARACTER_MAXIMUM_LENGTH,        NUMERIC_PRECISION,        NUMERIC_SCALE,        IS_NULLABLE,        COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IS_AUTOINCREMENT,        COLUMN_DEFAULT FROM   INFORMATION_SCHEMA.COLUMNS ORDER BY 2,3 Cheers Marco Antonio On Fri, Apr 25, 2008 at 5:43 PM, Steve Good <sgood@lanctr.com> wrote: > I did this once before, but forgot how I did it, and can't figure out how I ----- Excess quoted text cut - see Original Post for more -----

Cool. Anyway I'll suggest you to know about DataMgr. DataMgr do this job so easy. http://datamgr.riaforge.org/ With getDatabaseTables method and looping over calling getDBTableStruct method. Cheers Marco Antonio On Fri, Apr 25, 2008 at 6:19 PM, Steve Good <sgood@lanctr.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Cool, thanks for the tip, I'll look into it.   ~Steve http://lanctr.com/ Cool. Anyway I'll suggest you to know about DataMgr. DataMgr do this job so easy. http://datamgr.riaforge.org/ With getDatabaseTables method and looping over calling getDBTableStruct method. Cheers Marco Antonio On Fri, Apr 25, 2008 at 6:19 PM, Steve Good <sgood@lanctr.com> wrote: ----- Excess quoted text cut - see Original Post for more ----- how ----- Excess quoted text cut - see Original Post for more -----

In CF 8 <cfset MyDSN = "YourDSN"> <cfdbinfo     type="tables"     datasource="#MyDSN#"     name="tableMetaData"      > <table> <cfoutput query="tableMetaData">         <cfdbinfo                 type="columns"                 datasource="#MyDSN#"                 name="ColumnMetaData"                 table = "#TABLE_NAME#"                  > <tr>     <td><strong>#TABLE_NAME#</strong></td>     <td> </td>     <td> </td>     <td> </td>     <td>  </td> </tr> <cfloop  query="ColumnMetaData"> <tr>     <td>#COLUMN_NAME#</td>     <td>#TYPE_NAME# </td>     <td>#COLUMN_SIZE#</td>     <td>#IS_PRIMARYKEY#</td>     <td>#COLUMN_DEFAULT_VALUE#</td> </tr> </cfloop> </cfoutput> </table> On Fri, Apr 25, 2008 at 4:43 PM, Steve Good <sgood@lanctr.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Any way to get number of rows in each table? That would be REALLY useful ... In CF 8 <cfset MyDSN = "YourDSN"> <cfdbinfo     type="tables"     datasource="#MyDSN#"     name="tableMetaData"      > <table> <cfoutput query="tableMetaData">         <cfdbinfo                 type="columns"                 datasource="#MyDSN#"                 name="ColumnMetaData"                 table = "#TABLE_NAME#"                  > <tr>     <td><strong>#TABLE_NAME#</strong></td>     <td> </td>     <td> </td>     <td> </td>     <td>  </td> </tr> <cfloop  query="ColumnMetaData"> <tr>     <td>#COLUMN_NAME#</td>     <td>#TYPE_NAME# </td>     <td>#COLUMN_SIZE#</td>     <td>#IS_PRIMARYKEY#</td>     <td>#COLUMN_DEFAULT_VALUE#</td> </tr> </cfloop> </cfoutput> </table> On Fri, Apr 25, 2008 at 4:43 PM, Steve Good <sgood@lanctr.com> wrote: > I did this once before, but forgot how I did it, and can't figure out how ----- Excess quoted text cut - see Original Post for more -----

Cfdbinfo doesnt supply that You can sniff out the ID and so something like this: Select Count(#ID#) as NumberofRecords From #TABLE_NAME# Or some such. On Fri, Apr 25, 2008 at 6:11 PM, Colman, Richard <colman@codagenomics.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

SELECT     so.name as tableName     ,MAX(SIX.rows) as rowCounter FROM sysobjects SO INNER JOIN sysindexes SIX ON SIX.id = OBJECT_ID(SO.name) WHERE SO.xtype = 'U' GROUP BY SO.name ORDER BY 2 DESC On Fri, Apr 25, 2008 at 7:11 PM, Colman, Richard <colman@codagenomics.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

If you're on CF8, cfdbinfo is all you need. AHA! This looks familiar...  I'll give it a whirl.  Thanks! ~Steve http://lanctr.com/