i have 50 table in SQL server data base we are doing daily manually count

[dolly777]

i have 50 table in SQL server data base we are doing daily manually count but i dont want take like that.in qlikview table name and count and SQl table name and count display in qlikview in directly. we want create one qvd in that qvd field names are "table name" and "count of records"How to write a query.

Hi All,

Any one can help in this query?

[sujeetsingh · Accepted solution]

just write a sql script and load it in a qlikview table

[cwolf]

Hi Nirmala,

TTABLE:

select

TABLE_NAME as ALL_TABLES

from information_schema.TABLES where TABLE_SCHEMA='YOUR_SCHEMA_NAME' and TABLE_TYPE='BASE TABLE';

for n = 1 to fieldvaluecount('ALL_TABLES');

  let Table = fieldvalue('ALL_TABLES',$(n));

  TABLE_NOR:

  load

  '$(Table)' as TABLE_NAME,

  NUNMBER_OF_RECORDS;

  select count(*) as NUNMBER_OF_RECORDS from $(Table);

next

drop table TTABLE;

Regards

Christian

[cwolf]

Hi Nirmala,

if you have load your tables to QlikView Qvds, than you can also read the metadata of the qvd's to get the record numbers:

for each File in filelist ('Your_Directory\*.qvd')

QvdTableHeader:

LOAD

  QvBuildNo,

    CreatorDoc,

    CreateUtcTime,

    SourceCreateUtcTime,

    SourceFileUtcTime,

    SourceFileSize,

    StaleUtcTime,

    TableName,

    Compression,

    RecordByteSize,

    NoOfRecords,

    Offset,

    Length,

    Comment

FROM $(File) (XmlSimple, Table is [QvdTableHeader]);

next

Regards

Christian

[dolly777]

Hi Christian,

tried it...i  have confused if u dnt mind share ur mail id based on tht i send clear details

[cwolf]

Please accept my request to be your friend, then you see my email address.

[#7]

Hi Christian,

                         thanks for the reply

         here what is these

information_schema.TABLES where TABLE_SCHEMA='YOUR_SCHEMA_NAME' and TABLE_TYPE='BASE TABLE';

[cwolf]

Hi,

the information_schema is the data dictionary of SQL Server. Have a look at

INFORMATION_SCHEMA.TABLES tutorial and example

If you don't like to use it, you can also create an inline table with your table names:

TTABLE:

LOAD * INLINE [

  ALL_TABLES

  'Table1'                                                                                                                                                                'Table2'                                                                                                                                                                   ...

];

for ... next

[#9]

Hi Christian,

                       I used Inline function..Its working ..Thanks a lot..

Regards

Abinav