So, no-one has any help for me on this one... :)
I was hoping someone would have already done this before.
~Brad
-----Original Message-----
From: Brad Wood
Sent: Friday, January 26, 2007 2:37 PM
To: CF-Talk
Subject: dynamic form in SQL Server
Hey, I'm building a quick and dirty dynamic form to edit all the columns
in a table using SQL server 2000.
My select below works, but I know I should rewrite it using the
INFORMATION_SCHEMA approach.
Can anyone help me change this select to return the same info but not
use sysobjects etc.?
Thanks!
~Brad
SELECT
sysobjects.name AS table_name,
syscolumns.name AS column_name,
systypes.name AS column_type,
syscolumns.length,
syscolumns.prec,
syscolumns.scale,
syscolumns.isnullable,
sysproperties.value as label,
syscolumns.colstat & 1 as primary_key,
CASE WHEN sysforeignkeys.rkey IS NULL THEN 0 ELSE 1 END AS
foreign_key
FROM sysobjects WITH(NOLOCK)
INNER JOIN syscolumns WITH(NOLOCK) ON sysobjects.id = syscolumns.id
INNER JOIN systypes WITH(NOLOCK) ON syscolumns.xtype = systypes.xtype
LEFT OUTER JOIN sysforeignkeys WITH(NOLOCK) ON syscolumns.colid =
sysforeignkeys.fkey
AND sysobjects.id =
sysforeignkeys.fkeyID
LEFT JOIN sysproperties WITH(NOLOCK) ON sysproperties.id = syscolumns.id
AND sysproperties.smallid = syscolumns.colid
AND sysproperties.name = 'MS_Description'
WHERE sysobjects.name = @table_name
AND sysobjects.xtype = 'U' -- table
ORDER BY syscolumns.colorder
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267799
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
