Grant Select Privilege on SELECT_CATALOG_ROLE to User

In Oracle, after the new user or schema is created, the user cannot query the data dictionary. For example, if you run the below query:

Select * from dba_data_files

you'll see error message as ORA-00942: table or view does not exist

To allow the user to select table from Oracle data dictionary, you have to grant SELECT_CATALOG_ROLE privilege the that users. Use the following statement while logging in as sysdba

sql> grand SELECT_CATALOG_ROLE to user_name;

Amazon Product List Page

Click here to check out product list

Shop Amazon Books - New J.K. Rowling - The Casual Vacancy

Retrieve Table Information in MS SQL Server

Quite often there is a need to check number of records in the table. It can be done with ease with the following SQL statement

Select Count(*) from TableName

However, it would have been better if we can retrieve number of records from the table with other information related to that table. SQL Server provides this store procedure to accomplish this task

sp_spaceused 'Table_Name'

more detail inside this book

The statement above shall return result in the table format. This SQL statement only works with one table in the chosen database. If we want to loop through all tables in the database to display table information, we can perform this step.

• First, create the temporary table to store result from store procedure
• Second, run the store procedure
• Last, use the select statement to display the result

Use the below SQL statement to create the temporary table in SQL Server

CREATE TABLE #tableSize(
TAB_NAME NVARCHAR(128),
RowsNO VARCHAR(30),
sizereserved VARCHAR(30),
sizedata VARCHAR(30),
index_size VARCHAR(30),
unused VARCHAR(30) )

Then, execute the store procedure below

INSERT #tableSize EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

After the the procedure finish running, simply run the select statement.

SELECT * FROM #tableSize order by TAB_NAME

Find more books related to MS SQL Server