Hi ALL, I have to calculate for specific schema and for each table in this schema how many columns and how many records it has, I succeed to calculate the number of columns using the following request:
SELECT TableName, COUNT(ColumnName) " FROM DBC.ColumnsX WHERE DatabaseName = 'DB_NAME' GROUP BY (TableName) ORDER BY (TableName)
What I want is to have in the final output three columns: 1. TableName 2. Number of Columns 3. number of Records
I bet that I have to use either JOIN statement or improve my record in order to be dynamic one so I can get the output I need, please help me to find out a solution for my issue.
Can you write a Stored Procedure? If so, you can take the DatabaseName in as a parameter and iterate through all of the tables using a cursor. At each step, you do a count(*) and store the data in a temporary table with the database and table names. Then, just join to your above query and return a result set.
Good idea!Thank you mnylin, I thought to use PL-SQL, but I tried a simple example, however Teradata SQL assistant rejected it, it seems that I have to use a specific syntax, would you please give me an example, I'll check in the internet as well....