How do I get the list of all views present in a database and its record count?
For a validation purpose, I want to get the list of all the views present in a database and its corresponding record count. Can someone please help me to query this in SQL Server Management Studio?
sql-server asked Nov 12 '18 at 13:40
Anjali Anjali
11
add a comment |
For a validation purpose, I want to get the list of all the views present in a database and its corresponding record count. Can someone please help me to query this in SQL Server Management Studio?
sql-server asked Nov 12 '18 at 13:40
Anjali Anjali
11
stackoverflow.com/questions/2903262/…
– MusicLovingIndianGirl
Nov 12 '18 at 13:42
Possible duplicate of Sql Query to list all views in an SQL Server 2005 database
– Cid
Nov 12 '18 at 13:42
add a comment |
For a validation purpose, I want to get the list of all the views present in a database and its corresponding record count. Can someone please help me to query this in SQL Server Management Studio?
sql-server asked Nov 12 '18 at 13:40
Anjali Anjali
11
For a validation purpose, I want to get the list of all the views present in a database and its corresponding record count. Can someone please help me to query this in SQL Server Management Studio?
sql-server sql-server asked Nov 12 '18 at 13:40
Anjali Anjali
11
asked Nov 12 '18 at 13:40
Anjali Anjali
11
asked Nov 12 '18 at 13:40
Anjali Anjali
11
asked Nov 12 '18 at 13:40
Anjali Anjali
11
asked Nov 12 '18 at 13:40
Anjali Anjali
11
11
stackoverflow.com/questions/2903262/…
– MusicLovingIndianGirl
Nov 12 '18 at 13:42
Possible duplicate of Sql Query to list all views in an SQL Server 2005 database
– Cid
Nov 12 '18 at 13:42
add a comment |
stackoverflow.com/questions/2903262/…
– MusicLovingIndianGirl
Nov 12 '18 at 13:42
Possible duplicate of Sql Query to list all views in an SQL Server 2005 database
– Cid
Nov 12 '18 at 13:42
stackoverflow.com/questions/2903262/…
– MusicLovingIndianGirl
Nov 12 '18 at 13:42
stackoverflow.com/questions/2903262/…
– MusicLovingIndianGirl
Nov 12 '18 at 13:42
Possible duplicate of Sql Query to list all views in an SQL Server 2005 database
– Cid
Nov 12 '18 at 13:42
Possible duplicate of Sql Query to list all views in an SQL Server 2005 database
– Cid
Nov 12 '18 at 13:42
add a comment |
1 Answer
1
active
oldest
votes
Unfortunately, it isn't possible to retrieve rows count returned from a view by reading system tables, because this information is not maintained in the metadata. Only possible way is to actually execute select COUNT(*) from view_name, because views can contain complicated logic and the data returned by them actually is allocated to other objects (e.g. tables). For tables for example, you can get this rows count from sys.partitions DMV.
One workaround is to select the list of views and generate a dynamic query, which will execute select count(*) from each view and combine the results, but this could be quite heavy query.
Anyway, here is an example how to return a list of tables ('T') and views ('V') in your database. For tables you will also get a rows count, while for views this column will be null. Then change the where clause to return only views (where o.type = 'V') and execute the query. Copy/paste the last column in new query window (removing the last union all) and execute it to return the rows count for each view (NOTE: It could take a long time and have significant impact on your server!).
select o.name, sum(p.rows) as RowsCount, o.type, concat('select ''', o.name, ''' as ObjectName, count(*) as RowsCount from ', o.name, ' union all ')
from sys.objects o
left join sys.partitions p on p.object_id = o.object_id
where o.type in ('U', 'V')
group by o.name, o.type
order by o.name
answered Nov 12 '18 at 14:09
Andrey NikolovAndrey Nikolov
3,6781621
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263435%2fhow-do-i-get-the-list-of-all-views-present-in-a-database-and-its-record-count%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Unfortunately, it isn't possible to retrieve rows count returned from a view by reading system tables, because this information is not maintained in the metadata. Only possible way is to actually execute select COUNT(*) from view_name, because views can contain complicated logic and the data returned by them actually is allocated to other objects (e.g. tables). For tables for example, you can get this rows count from sys.partitions DMV.
One workaround is to select the list of views and generate a dynamic query, which will execute select count(*) from each view and combine the results, but this could be quite heavy query.
Anyway, here is an example how to return a list of tables ('T') and views ('V') in your database. For tables you will also get a rows count, while for views this column will be null. Then change the where clause to return only views (where o.type = 'V') and execute the query. Copy/paste the last column in new query window (removing the last union all) and execute it to return the rows count for each view (NOTE: It could take a long time and have significant impact on your server!).
select o.name, sum(p.rows) as RowsCount, o.type, concat('select ''', o.name, ''' as ObjectName, count(*) as RowsCount from ', o.name, ' union all ')
from sys.objects o
left join sys.partitions p on p.object_id = o.object_id
where o.type in ('U', 'V')
group by o.name, o.type
order by o.name
answered Nov 12 '18 at 14:09
Andrey NikolovAndrey Nikolov
3,6781621
add a comment |
Unfortunately, it isn't possible to retrieve rows count returned from a view by reading system tables, because this information is not maintained in the metadata. Only possible way is to actually execute select COUNT(*) from view_name, because views can contain complicated logic and the data returned by them actually is allocated to other objects (e.g. tables). For tables for example, you can get this rows count from sys.partitions DMV.
One workaround is to select the list of views and generate a dynamic query, which will execute select count(*) from each view and combine the results, but this could be quite heavy query.
Anyway, here is an example how to return a list of tables ('T') and views ('V') in your database. For tables you will also get a rows count, while for views this column will be null. Then change the where clause to return only views (where o.type = 'V') and execute the query. Copy/paste the last column in new query window (removing the last union all) and execute it to return the rows count for each view (NOTE: It could take a long time and have significant impact on your server!).
select o.name, sum(p.rows) as RowsCount, o.type, concat('select ''', o.name, ''' as ObjectName, count(*) as RowsCount from ', o.name, ' union all ')
from sys.objects o
left join sys.partitions p on p.object_id = o.object_id
where o.type in ('U', 'V')
group by o.name, o.type
order by o.name
answered Nov 12 '18 at 14:09
Andrey NikolovAndrey Nikolov
3,6781621
add a comment |
Unfortunately, it isn't possible to retrieve rows count returned from a view by reading system tables, because this information is not maintained in the metadata. Only possible way is to actually execute select COUNT(*) from view_name, because views can contain complicated logic and the data returned by them actually is allocated to other objects (e.g. tables). For tables for example, you can get this rows count from sys.partitions DMV.
One workaround is to select the list of views and generate a dynamic query, which will execute select count(*) from each view and combine the results, but this could be quite heavy query.
Anyway, here is an example how to return a list of tables ('T') and views ('V') in your database. For tables you will also get a rows count, while for views this column will be null. Then change the where clause to return only views (where o.type = 'V') and execute the query. Copy/paste the last column in new query window (removing the last union all) and execute it to return the rows count for each view (NOTE: It could take a long time and have significant impact on your server!).
select o.name, sum(p.rows) as RowsCount, o.type, concat('select ''', o.name, ''' as ObjectName, count(*) as RowsCount from ', o.name, ' union all ')
from sys.objects o
left join sys.partitions p on p.object_id = o.object_id
where o.type in ('U', 'V')
group by o.name, o.type
order by o.name
answered Nov 12 '18 at 14:09
Andrey NikolovAndrey Nikolov
3,6781621
Unfortunately, it isn't possible to retrieve rows count returned from a view by reading system tables, because this information is not maintained in the metadata. Only possible way is to actually execute select COUNT(*) from view_name, because views can contain complicated logic and the data returned by them actually is allocated to other objects (e.g. tables). For tables for example, you can get this rows count from sys.partitions DMV.
One workaround is to select the list of views and generate a dynamic query, which will execute select count(*) from each view and combine the results, but this could be quite heavy query.
Anyway, here is an example how to return a list of tables ('T') and views ('V') in your database. For tables you will also get a rows count, while for views this column will be null. Then change the where clause to return only views (where o.type = 'V') and execute the query. Copy/paste the last column in new query window (removing the last union all) and execute it to return the rows count for each view (NOTE: It could take a long time and have significant impact on your server!).
select o.name, sum(p.rows) as RowsCount, o.type, concat('select ''', o.name, ''' as ObjectName, count(*) as RowsCount from ', o.name, ' union all ')
from sys.objects o
left join sys.partitions p on p.object_id = o.object_id
where o.type in ('U', 'V')
group by o.name, o.type
order by o.name
answered Nov 12 '18 at 14:09
Andrey NikolovAndrey Nikolov
3,6781621
answered Nov 12 '18 at 14:09
Andrey NikolovAndrey Nikolov
3,6781621
answered Nov 12 '18 at 14:09
Andrey NikolovAndrey Nikolov
3,6781621
answered Nov 12 '18 at 14:09
Andrey NikolovAndrey Nikolov
3,6781621
3,6781621
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263435%2fhow-do-i-get-the-list-of-all-views-present-in-a-database-and-its-record-count%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
stackoverflow.com/questions/2903262/…
– MusicLovingIndianGirl
Nov 12 '18 at 13:42
Possible duplicate of Sql Query to list all views in an SQL Server 2005 database
– Cid
Nov 12 '18 at 13:42