Tag: Database

How to: Query All Tables’ Row Count, Data Space & Index Space

Query all tables’ row count, data space & index space in SQL Server

SET NOCOUNT ON;

DBCC UPDATEUSAGE(0);

CREATE TABLE #t
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
);

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''';

SELECT * FROM #t
ORDER BY [name];
 
DROP TABLE #t ;

SQL Server Database Project

  1. On the File menu, point to New, and then click Project to add SQL Server 2008 Database Project to the existing solution.
  2. On the Data menu, point to Schema Compare, and then click New Schema Comparison.
  3. In the Source Schema panel, select Database, and then click New Connection to connect to the data source.
  4. In the Target Schema panel, select Project, and then select the Database Project.
  5. Click OK to start Schema Comparison.
  6. After the comparison finishes, a table in the Schema Compare window lists the database objects that were compared.
  7. To update the schema to the target Database Project, click Write Updates button in the Schema Compare toolbar.
  8. You can save the created Schema Comparison for later use.