CODEWITHSUNDEEP
sql-servert-sqlviewinformation-schema
E.YILDIRIM
E.YILDIRIM

Reputation: 27

SQL INFORMATION_SCHEMA.VIEWS

I want to make 2020 that include 2019 for all view contents, but I have a problem with the long views.Is View Definition column has a character limit? Because as far as I can see, there is a problem with views that are too long codes.Is there a remedy?

declare @Icerik varchar(max)
declare pcursor cursor
for
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME like '%2019%'
open pcursor
fetch next from pcursor into @Icerik
    while @@FETCH_STATUS = 0
        begin
            set @Icerik = replace(@Icerik,'2019','2020')
            exec (@Icerik)
            fetch next from pcursor into @Icerik
        end
    close pcursor
    deallocate pcursor

Upvotes: 0

Views: 692

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

Is View Definition column has a character limit?

From INFORMATION_SCHEMA.VIEWS:

VIEW_DEFINITION nvarchar(4000) If the length of definition is larger than nvarchar(4000), this column is NULL. Otherwise, this column is the view definition text.

I suggest to use sys.sql_modules

definition nvarchar(max) SQL text that defines this module. This value can also be obtained using the OBJECT_DEFINITION built-in function.

db<>fiddle

Alternatively SELECT OBJECT_DEFINITION(OBJECT_ID('schema.view_name'))

Upvotes: 1

Related Questions