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

  • 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?

  • Any help? ?

    • This reply was modified 1 month ago by  GG_BI_GG.
  • 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
  • -- 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?

    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

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

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