Determining Formula Field

Question

Hi all,

Guys, I am using SQL Server 2000 and executing the sp_columns stored procedure to get a layout of my table. One of my fields is a formula field and my question is, how can I determine this through sp_columns? sp_columns does not seem to show this information.

THanks in advance

Answer 1

You could dump the results of sp_columns to a temp table, and then add on the ColumnProperty function to the results of that...

create table #results(
    TABLE_QUALIFIER sysname,
    TABLE_OWNER sysname,
    TABLE_NAME sysname,
    COLUMN_NAME sysname,
    DATA_TYPE smallint,
    TYPE_NAME sysname,
    PRECISION int,
    LENGTH int,
    SCALE smallint,
    RADIX smallint,
    NULLABLE smallint,
    REMARKS varchar(254),
    COLUMN_DEF nvarchar(4000),
    SQL_DATA_TYPE smallint,
    SQL_DATETIME_SUB smallint,
    CHAR_OCTET_LENGTH int,
    ORDINAL_POSITION int,
    IS_NULLABLE varchar(254),
    SS_DATA_TYPE tinyint)

insert  #results
exec sp_columns 'MyTable'

select  IsComputed = ColumnProperty(object_id(table_owner + '.' + table_name), column_name, 'IsComputed'),
     *
from    #results

Answer 2

SELECT name FROM syscolumns where id IN(
SELECT ID FROM sysobjects where name = 'My Table' and xtype ='U')
and IsComputed = 1

Raj