Monday, September 10, 2018

How to Find the number of rows,allocated size, rows size, index size and free space in a table


exec sp_spaceused 'AddressType'

name                  rows   reserved     data        index_size unused
AddressType         6        48 KB         8 KB       40 KB 0 KB

It returns number of rows, reserved space for the table, data size, index size and unused space left.

We can use above to find for one table.
When we tried to find all the tables in a database then we can achieve by writing t-SQL script

 --Declare the table variable to store the table names

DECLARE @Tables TABLE (SchemaTable varchar(100));

DECLARE @TableName varchar(100); --To store each table name


-- Insert table names into the table variable
INSERT @Tables (SchemaTable)
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'

--Loop through the table names
WHILE (SELECT COUNT(*) FROM @Tables) > 0
BEGIN
SELECT TOP 1 @TableName = SchemaTable
FROM @Tables
ORDER BY SchemaTable;
EXEC sp_spaceused @TableName;
DELETE @Tables
WHERE SchemaTable = @TableName;
END;

This will give the list of all the tables.

SSRS : How to fit PDF export on one page in reporting services avoid column splitting to multiple pages

In SSRS when exporting all the columns and records to PDF, if it is larger column volumes then it is getting splitted to 2nd page or multiple pages.





To fix this follow the below steps.

1. Go to Report Properties ->Interactive Size 
2. Make sure width and Height is more than the report Body width and height size.
3. change the report Properties-> Margins to .5 in

Run the Report. Now all the columns will fit to the one page.