From the sys.sql_modules (linked.db.schema.vw_xxx) seperate column

Question

Post reply

From the sys.sql_modules (linked.db.schema.vw_xxx) seperate column

GG_BI_GG

SSCrazy

Points: 2627

October 27, 2021 at 3:59 pm

#3945118

Hi All,

Im working an a issue and have some thinks I would like to fix it.

At moment I need the definition of a all views, im jusing the follwoing query to find the definition (SYNTAX):

SELECT
    definition,
    uses_ansi_nulls,
    uses_quoted_identifier,
    is_schema_bound
FROM
    sys.sql_modules
WHERE
    object_id
    = object_id(
            'view'
        );

All the views is a select statement of a table in a different (now looking at one DB) database, I would like the from statement but only the value where is coming from.

The following looks like

SELECT column1, column2 FROM linkedsrv.schema.dbo.vw_xx

-- lnksrv    | schema | dbo | view 
-- linkedsrv   schema   dbo   vw_xx

or

SELECT column1, column2 FROM schema.dbo.vw_xx

-- lnksrv    | schema | dbo | view 
-- null        schema   dbo   vw_xx

I have search a little but and found this and works good when you just look for only 1 view, but maybe is possible to make a cursor of it and get the output in a table based on the example above, seperate them in columns.

declare @delimiter nvarchar(2) = char(10);
declare @objectName sysname = '[schema].[vw_xxx]'

;with CTE as (
  select 
    0 as linenr
  , object_definition( object_id(@objectName)) as def
  , convert(nvarchar(max), N'') as line
  union all
  select 
    linenr + 1
  , substring(def, charindex(@delimiter, def) + len(@delimiter), len(def) - (charindex(@delimiter, def)))
  , left(def, charindex(@delimiter, def)) as line
  from CTE
  where charindex(@delimiter, def) <> 0
 )
selectlinenr, 
line,
[Left] = LEFT(line, CHARINDEX('[', line, 0) - 1),
        Reverse_Left = REVERSE(LEFT(line, CHARINDEX('[', line, 0) - 1)),
from CTE
where linenr >= 1 and line like 'FROM%'
OPTION (MAXRECURSION 0);

CAN SOMEBODY help me with this? And is it possible?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 1

Any help? ?

This reply was modified 3 years, 5 months ago by GG_BI_GG.

DNA_DBA SSCrazy Eights Points: 9134 More actions · Answer 2

Does the following give you what you want?

select
   v.name,
   sed.referenced_server_name,
   referenced_database_name,
   referenced_schema_name,
   referenced_entity_name 
from
   sys.sql_expression_dependencies sed 
   join
      sys.views v 
      on sed.referencing_id = v.object_id 
order by
   v.name

Johan Bijnens SSC Guru Points: 135449 More actions · Answer 3

-- was to quick --

How about:

If you are able to extract the 4-part-named object usage

create a schema e.g. [work], generate and execute a

'select * into work.[4-part-named-object] from 4-part-named-object where 0 = 1 ;'

and extract the ddl for the work.* objects.

Would that meet your needs?

This reply was modified 3 years, 5 months ago by Johan Bijnens.
This reply was modified 3 years, 5 months ago by Johan Bijnens.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

From the sys.sql_modules (linked.db.schema.vw_xxx) seperate column

Cookies on SQLServerCentral