In QA,
SELECT Table_Name, View_Definition
FROM Information_Schema.Views
Table_Name View_Definition
ThisView CREATE VIEW...
ThatView CREATE VIEW...
OtherView NULL <-- HUH?!?
So I flip over to EM and double-click 'OtherView' and I can see the code in
the property page that appears. What does that mean?
Peace & happy computing,
Mike Labosh, MCSD
"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"Mike,
No idea, but try refreshing all views before selecting from
information_schema.views
Example:
use northwind
go
declare @.ts sysname
declare @.tn sysname
declare @.sql nvarchar(4000)
declare views_cursor cursor local fast_forward
for
select
table_schema,
table_name
from
information_schema.tables
where
table_type = 'view'
and objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsMSShipped') = 0
open views_cursor
while 1 = 1
begin
fetch next from views_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'exec sp_refreshview ''' + quotename(@.ts) + N'.' +
quotename(@.tn) + ''''
exec sp_executesql @.sql
end
close views_cursor
deallocate views_cursor
go
AMB
"Mike Labosh" wrote:
> In QA,
> SELECT Table_Name, View_Definition
> FROM Information_Schema.Views
> Table_Name View_Definition
> ThisView CREATE VIEW...
> ThatView CREATE VIEW...
> OtherView NULL <-- HUH?!?
> So I flip over to EM and double-click 'OtherView' and I can see the code i
n
> the property page that appears. What does that mean?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Mr. McKittrick, after very careful consideration, I have
> come to the conclusion that this new system SUCKS."
> -- General Barringer, "War Games"
>
>|||> SELECT Table_Name, View_Definition
> FROM Information_Schema.Views
> Table_Name View_Definition
> ThisView CREATE VIEW...
> ThatView CREATE VIEW...
> OtherView NULL <-- HUH?!?
I GOT IT!
Inside Information_Schema.Views, it does this:
-- Displays views accessable to current user
create view INFORMATION_SCHEMA.VIEWS
as
select
db_name() as TABLE_CATALOG
,user_name(obj.uid) as TABLE_SCHEMA
,obj.name as TABLE_NAME
,case
when exists (select *
from syscomments com3
where com3.id = obj.id
and com3.colid > 1) then convert(nvarchar(4000), NULL)
else com.text
end as VIEW_DEFINITION
If the text of the view is greater than 4000 characters, the convert
function returns null.
Rats.
So where does EM get the code from, to display in the View's property page?
Peace & happy computing,
Mike Labosh, MCSD
"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"|||What about quering the tables direct without the information_views...
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Labosh" <mlabosh@.hotmail.com> schrieb im Newsbeitrag
news:etaHXDvZFHA.2788@.TK2MSFTNGP12.phx.gbl...
> I GOT IT!
> Inside Information_Schema.Views, it does this:
> -- Displays views accessable to current user
> create view INFORMATION_SCHEMA.VIEWS
> as
> select
> db_name() as TABLE_CATALOG
> ,user_name(obj.uid) as TABLE_SCHEMA
> ,obj.name as TABLE_NAME
> ,case
> when exists (select *
> from syscomments com3
> where com3.id = obj.id
> and com3.colid > 1) then convert(nvarchar(4000), NULL)
> else com.text
> end as VIEW_DEFINITION
>
> If the text of the view is greater than 4000 characters, the convert
> function returns null.
> Rats.
> So where does EM get the code from, to display in the View's property
> page?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Mr. McKittrick, after very careful consideration, I have
> come to the conclusion that this new system SUCKS."
> -- General Barringer, "War Games"
>
