January 26, 2024 at 5:08 pm
This SQL renders all articles in a PUB including TBLs, VWs, Procs and UDF's:
SELECT * FROM distribution.dbo.msarticles WHERE publication_id = 6 order by article
Is there a way to EXCLUDE the Procs and UDF names from this result? thx in advance.
January 26, 2024 at 5:14 pm
If you want to exclude something that is a row, you would need to update the WHERE clause to exclude the data you don't need. If you are trying to exclude columns, then change the "*" to the column list you want to pull in.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 26, 2024 at 5:24 pm
Thx for your feedback. As of SQL 2019, -- unfortunately there are no columns in distribution.dbo.msarticles table which differentiate TBLs from VWs from PROCs from UDFs -- so I cannot add the PREDICATE to omit Procs and UDF's. All are returned but I only want to see TBLs and VWs. Was hoping I could join to a TBL to apply this predicate.
January 26, 2024 at 5:34 pm
What about joining on sys.objects? You could join on the OBJECT_ID(msarticles.name) = objects.object_id from objects and you should be good to go, right?
That would allow to filter out the object types you care about.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 26, 2024 at 5:44 pm
thx.. let me give that a try..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply