Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic view

Author  Topic 

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-07-22 : 14:50:01
I have a view created from only one table.
VW_Stats ( Tab_Name,Load_Status,User,....)

Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.

To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection

Table_name Load_Status RecordCount User...
tbl_collection Running 1244 XYZ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 14:56:51
You can't do it in the view, so you'll need to calculate it separately and then add the value to your result set.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-07-22 : 15:12:54
A function call to get the count(*) by passing the table name as a parameter?

-Neil
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-22 : 16:42:09
It's not clear what you what to do. Please follow the links below for how to ask your question:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-07-22 : 16:51:00
I am sorry, I've problem with my chrome & Ie, they are not allowing me to post 2-3 lines messages. I think I got the rt hint on this from Tkizer, to use function by passing the table name to get the result.

-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-07-23 : 06:10:36
I have a view created from only one table.
VW_Stats ( Tab_Name,Load_Status,User,....)

Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table,
The Stats table from which the view is created, is used for capturing the current data load stats, the data is being loaded in to several tables from source, I am trying to get the number of records loaded into each table during the data load still running.

Select * from Vw_Stats

Table_name Load_Status User...
tbl_collection Running XYZ

To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection

Table_name Load_Status RecordCount User...
tbl_collection Running 1244 XYZ


is this possible?

create view vw_stats(
Table_name,
Load_status,
(Select count(*) from Table_name) as RecordCount, -- I would like to know how to make this work
User,
from tbl_stats



-Neil
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-23 : 12:00:52
If you wanted you can query the DMVs. There are certain levels of permissions that you might need. Here is a link that describes several ways to get a row count for a table and the permissions needed:
http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

Here is a sample of how you might go about putting it in a view:
-- Create table to test
CREATE TABLE tbl_collection (id int);
INSERT tbl_collection VALUES (1), (2), (3);


CREATE TABLE tbl_stats (Table_name VARCHAR(128));
INSERT tbl_stats VALUES ('tbl_collection');

-- Main Query
SELECT
o.name,
ddps.row_count
FROM
sys.indexes AS i
INNER JOIN
sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN
tbl_stats AS tbl
ON o.name = tbl.Table_name
INNER JOIN
sys.dm_db_partition_stats AS ddps
ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE
i.index_id < 2
AND o.is_ms_shipped = 0;

-- Cleanup
DROP TABLE tbl_collection;
DROP TABLE tbl_stats;
NOTE: It'd probably be a good idea to add the Table Schema as a separate column to your tbl_stats table to help minimize issues in the future.
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-07-23 : 15:31:22
Thanks Lamprey, I'll try this

-Neil
Go to Top of Page
   

- Advertisement -