Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Printing Database Structure
Author: Dawson, Michael
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304286
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/
Author: Web Specialist
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304280
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
----- Excess quoted text cut - see Original Post for more -----
Author: Gerald Guido
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304279
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.
----- Excess quoted text cut - see Original Post for more -----
Author: Colman, Richard
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304278
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>
> 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 -----
Author: Gerald Guido
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304277
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>
----- Excess quoted text cut - see Original Post for more -----
Author: Steve Good
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304275
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
----- Excess quoted text cut - see Original Post for more -----
how
----- Excess quoted text cut - see Original Post for more -----
Author: Web Specialist
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304273
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
----- Excess quoted text cut - see Original Post for more -----
Author: Brad Wood
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304272
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/
Author: Steve Good
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304271
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
> 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 -----
Author: Steve Good
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304270
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
> 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 -----
Author: Steve Good
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304269
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 -----
Author: Web Specialist
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304268
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
----- Excess quoted text cut - see Original Post for more -----
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304267
>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 -----
Author: Steve Good
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56186#304264
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/
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
