I've built something similar that lets me create a SQL Script dump
file similar to what MySQL allows. Here's the "starting idea" for the
query -
SELECT SO.Name as TableName, SC.Name as ColumnName,
ST.Name + CASE WHEN ST.Status = 2 THEN '(' + CONVERT(VarChar(6),
SC.Length) + ')' ELSE
CASE WHEN SC.AutoVal IS NOT NULL THEN ' IDENTITY' END END as ColumnType,
CASE WHEN Left(SysIndexLookup.IndexType, 2) = 'PK' THEN 'PRIMARY KEY'
ELSE '' END as KeyText
FROM SYSOBJECTS SO INNER JOIN SysColumns SC ON
SO.ID = SC.ID INNER JOIN SysTypes ST ON
SC.xtype = ST.xtype LEFT OUTER JOIN
(SELECT SI.id, SI.Name as IndexType, SIK.colid
FROM SysIndexes SI INNER JOIN SysIndexKeys SIK ON
SI.ID = SIK.ID AND
SI.IndID = SIK.IndID) SysIndexLookup ON
SO.ID = SysIndexLookup.ID AND SC.ColID = SysIndexLookup.ColID
WHERE so.xtype = 'U'
Best of luck!
Hatton
On 10/21/06, Pete Ruckelshaus <[EMAIL PROTECTED]> wrote:
> Slightly Off-Topic SQL question:
>
> Using SQL Server 2000, is there any way to get (a) a list of
> non-system table names for a given database, and (b) any way to get
> the datatypes for database columns? This would be via two different
> queries.
>
> I want to create a CF-based utility that I can "point" at a DSN, read
> the table names, return that list to the user so they can select which
> tables to back up; then, those tables that are chosen to be backed up,
> I will do the backup much the way MySQL does, by creating an SQL
> script that is all insert statements. However, in order to write the
> insert statements correctly, I need to know whether to quote the
> values being inserted.
>
> Moreover, it just seemed like a fun project to work on :)
>
> Thanks,
>
> Pete
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257680
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
