Identifying computed columns in a database 1

[divinyl]

Hi all,

Is there some sort of sproc or query that i can run to identify all the computed columns within tables, within a particular db?

Thx!

Div

 

[ptheriault]

This will work for SQL 2005

[COLOR=blue]SELECT[/color] o.name
      ,c.name
      ,co.text
 [COLOR=blue]FROM[/color] sys.objects o [COLOR=blue]JOIN[/color] sys.columns c
       [COLOR=blue]on[/color] o.object_id = c.object_id
      [COLOR=blue]JOIN[/color] sys.syscomments co
       [COLOR=blue]on[/color] c.object_id = co.id and c.column_id = co.colid 
[COLOR=blue]WHERE[/color] c.is_computed = 1

This for 2000.

[COLOR=blue]SELECT[/color]    syscolumns.name, syscomments.text
  [COLOR=blue]FROM[/color]    sysobjects, syscomments, syscolumns
 [COLOR=blue]WHERE[/color]  sysobjects.id = syscolumns.id AND
        syscolumns.id = syscomments.id AND
       syscolumns.colid = syscomments.colid AND
       syscolumns.iscomputed = 1

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[divinyl]

thanks for getting back to me - but ive run your SQL 2000 query against my database and i dont really understand the output. In the name column, you have names like @@EventTime or @Failed url, and in the text column you have something really weird & huge like:

<code>
CREATE PROCEDURE proc_MatchSubscriptions( @@EventTime datetime OUTPUT) AS SET NOCOUNT ON SET @@EventTime = GETUTCDATE() INSERT INTO EventLog( SiteId, NotifyFreq, EventTime, ListId, ItemId, ItemName, ItemFullUrl, EventType, ModifiedBy, TimeLastModified) SELECT DISTINCT EventCache.SiteId, SchedSubscriptions.NotifyFreq, EventCache.EventTime, EventCache.ListId, EventCache.ItemId, EventCache.ItemName, EventCache.ItemFullUrl, EventCache.EventType, EventCache.ModifiedBy, EventCache.TimeLastModified FROM EventCache INNER JOIN SchedSubscriptions ON EventCache.SiteId = SchedSubscriptions.SiteId AND EventCache.ListId = SchedSubscriptions.ListId AND (SchedSubscriptions.ItemId IS NULL OR EventCache.ItemId = SchedSubscriptions.ItemId) AND (EventCache.EventType & SchedSubscriptions.EventType <> 0) WHERE EventCache.EventTime < @@EventTime IF (@@ERROR <> 0) RETURN 31 SELECT ImmedSubscriptions.WebId, ImmedSubscriptions.UserEmail, EventCache.EventType, ImmedSubscriptions.SiteUrl, ImmedSubscriptions.WebUrl, ImmedSubscriptions.ListUrl, EventCache.ItemId, ImmedSubscriptions.WebTitle, ImmedSubscriptions.WebLanguage, ImmedSubscriptions.WebLocale, ImmedSubscriptions.WebTimeZone, ImmedSubscriptions.WebTime24, ImmedSubscriptions.WebCalendarType, ImmedSubscriptions.WebAdjustHijriDays, ImmedSubscriptions.ListTitle, ImmedSubscriptions.ListBaseType, EventCache.ItemName, EventCache.ItemFullUrl, EventCache.ModifiedBy, EventCache.TimeLastModified, ImmedSubscriptions.Id, ImmedSubscriptions.ItemId, ImmedSubscriptions.EventType FROM EventCache INNER JOIN ImmedSubscriptions ON EventCache.SiteId = ImmedSubscriptions.SiteId AND EventCache.ListId = ImmedSubscriptions.ListId AND (ImmedSubscriptions.ItemId IS NULL OR EventCache.ItemId = ImmedSubscriptions.ItemId) AND (EventCache.EventType & ImmedSubscriptions.EventType <> 0) WHERE EventCache.EventTime < @@EventTime AND ImmedSubscriptions.UserEmail <> N'' ORDER BY ImmedSubscriptions.SiteId, ImmedSubscriptions.WebId, ImmedSubscriptions.UserEmail, ImmedSubscriptions.ListUrl

</code>

????

Thanks
Div

 

[ptheriault]

It would appear that you have a computed column that is calling this stored procedure proc_MatchSubscriptions
What you are seeing is the definition of the procedure.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I made a quick change to these.
here is the 2005

[COLOR=blue]select[/color] o.name
      ,c.name
      ,cm.text 
[COLOR=blue]from[/color] sys.columns c [COLOR=blue]JOIN[/color] sys.syscomments cm
     [COLOR=blue]on[/color] c.object_id = cm.id
     [COLOR=blue]JOIN[/color] sys.objects o
     [COLOR=blue]on[/color] c.object_id = o.object_id
[COLOR=blue]WHERE[/color] is_computed = 1

and 2000 syntax

[COLOR=blue]select[/color] o.name
      ,c.name
      ,cm.text 
[COLOR=blue]from[/color] syscolumns c [COLOR=blue]JOIN[/color] syscomments cm
     [COLOR=blue]on[/color] c.id = cm.id
     [COLOR=blue]JOIN[/color] sysobjects o
     [COLOR=blue]on[/color] c.id = o.id
[COLOR=blue]WHERE[/color] iscomputed = 1

- Paul

- If at first you don't succeed, find out if the loser gets anything.