Brad,
Here is a system stored proc for getting columns:
<cfstoredproc procedure="sp_columns" datasource="#variables.dsn#">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR"
value="#arguments.table#" null="No">
<cfprocresult name="getColSP">
</cfstoredproc>
sp_tables gets tables, sp_pkeys gets primary keys.
HTH,
Sam
On 1/26/07, Brad Wood <[EMAIL PROTECTED]> wrote:
> 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:267802
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
