Computed Columns Index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Computed Columns Index

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2003-12-16 : 13:48:25

Hi Folks,
Is there way to find out if there is a index on a computed column in a table. Also is there way to find out computed columns on all the tables in a database.

Thank you
Ramdas

Ramdas Narayanan
SQL Server DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 14:10:50

This will write out the code needed to figure out if the column is computed or not:

SELECT 'select COLUMNPROPERTY(OBJECT_ID(''' + TABLE_NAME + '''), ''' + COLUMN_NAME + ''', ''IsComputed'')'
FROM INFORMATION_SCHEMA.COLUMNS

Run the output. You will get a 1 if it is computed. I'll work on the index thing now.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 14:18:26

I don't have any computed columns to test this (even in pubs and Northwind), but:

SELECT OBJECT_NAME(id) AS TableName, COL_NAME(id, colid) AS ColumnName
FROM sysindexkeys
WHERE COLUMNPROPERTY(id, COL_NAME(id, colid), 'IsComputed') = 1

Tara

ramdas
Posting Yak Master

181 Posts

Posted - 2003-12-17 : 13:45:49

Hi Folks,
Thanks for your help.
Ramdas Narayanan

Ramdas Narayanan
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources