July 13, 2015 at 9:17 am
I use the following code to list replicated tables:
SELECT Name
FROM sys.views
WHERE is_replicated = 1
ORDER BY Name
I need to list Views and functions. How do I do that?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 13, 2015 at 9:24 am
use sys.objects instead of sys.views, and use a filter on type_desc column; WHERE type_desc IN('VIEW','ETC....)
SELECT DISTINCT type_desc FROM sys.objects
select which types you want to use.
Lowell
July 13, 2015 at 9:35 am
The following does not work:
SELECT Name
FROM sys.Objects
WHERE is_replicated = 1
AND type_desc IN ('VIEW')
ORDER BY Name
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 13, 2015 at 9:51 am
it looks to me like only sys.tables and sys.views have the is_replicated column you are looking for.
select object_name(object_id) from sys.all_columns where name ='is_replicated'
Lowell
July 13, 2015 at 11:06 am
Actually I'm looking for the objects (Views & Functions) not the Columns.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 14, 2015 at 12:00 am
There is also a column is_published , that might help !
select top 100 * from sys.objects
where is_published = 1
order by 1
July 14, 2015 at 6:06 am
r.dragoi-1095738 (7/14/2015)
There is also a column is_published , that might help !select top 100 * from sys.objects
where is_published = 1
order by 1
That only returns tables.
I'm looking for Views and Functions.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 14, 2015 at 6:34 am
-- use publishing_db
-- GO
select a.name, b.type_desc
from sysarticles a
inner join sys.objects b on a.objid = b.object_id
July 14, 2015 at 6:52 am
Then you could try something like this :
SELECT name AS published_object, schema_id, is_published AS is_tran_published, is_merge_published, is_schema_published
FROM sys.tables WHERE is_published = 1 or is_merge_published = 1 or is_schema_published = 1
UNION
SELECT name AS published_object, schema_id, 0, 0, is_schema_published
FROM sys.procedures WHERE is_schema_published = 1
UNION
SELECT name AS published_object, schema_id, 0, 0, is_schema_published
FROM sys.views WHERE is_schema_published = 1;
check here : https://msdn.microsoft.com/en-us/library/ms151797.aspx
November 5, 2024 at 6:00 pm
SELECT schema_name(o.schema_id) + '.' + name AS published_object, o.type_desc
FROM sys.objects o
WHERE o.is_schema_published = 1
or o.is_published = 1
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy