October 27, 2021 at 3:59 pm
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?
October 29, 2021 at 11:41 am
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
October 29, 2021 at 12:35 pm
-- 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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply