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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply