-- -- query 1 -- select s.name, o.name, o.type_desc from sys.objects o join sys.schemas s on o.schema_id = s.schema_id where is_ms_shipped = 0 and s.name in ('DIM', 'FACT') order by o.type -- -- query 2 -- select s.name as the_schema_name, t.name as the_table_name, OBJECTPROPERTYEX(t.object_id, 'Cardinality') as the_row_count from sys.tables t join sys.schemas s on t.schema_id = s.schema_id where is_ms_shipped = 0 and s.name in ('DIM', 'FACT'); -- -- query 3 -- select o.type_desc as object_type, COUNT(*) as object_count from sys.objects o join sys.schemas s on o.schema_id = s.schema_id where is_ms_shipped = 0 and s.name in ('DIM', 'FACT') group by o.type_desc order by o.type_desc -- -- query 4 -- CREATE DATABASE [BIG_JONS_BBQ_DW2] ON (FILENAME = 'C:\MSSQL\DATA\BIG_JONS_BBQ_DW2.mdf'), (FILENAME = 'C:\MSSQL\LOG\BIG_JONS_BBQ_DW2.ldf') FOR ATTACH ; -- -- query 5 -- SELECT c.cus_qtr_key, count(*) as cus_total FROM [FACT].[CUSTOMERS] c GROUP BY c.cus_qtr_key ORDER BY c.cus_qtr_key; -- -- query 6 -- SELECT c.cus_qtr_key, c.cus_package_key, count(*) as cus_total FROM [FACT].[CUSTOMERS] c GROUP BY c.cus_qtr_key, c.cus_package_key ORDER BY c.cus_qtr_key, c.cus_package_key; -- -- query 7 -- DELETE FROM [FACT].[CUSTOMERS] WHERE cus_qtr_key = 2011001 -- -- query 8 -- USE [BBQ_PART_VIEW]; EXECUTE ('ALTER VIEW [FACT].[CUSTOMERS] WITH SCHEMABINDING AS SELECT [cus_id] ,[cus_lname] ,[cus_fname] ,[cus_phone] ,[cus_address] ,[cus_city] ,[cus_state] ,[cus_zip] ,[cus_package_key] ,[cus_start_date_key] ,[cus_end_date_key] ,[cus_date_str] ,[cus_qtr_key] FROM [FACT].[CUSTOMERS_2011002] UNION ALL SELECT [cus_id] ,[cus_lname] ,[cus_fname] ,[cus_phone] ,[cus_address] ,[cus_city] ,[cus_state] ,[cus_zip] ,[cus_package_key] ,[cus_start_date_key] ,[cus_end_date_key] ,[cus_date_str] ,[cus_qtr_key] FROM [FACT].[CUSTOMERS_2011003] UNION ALL SELECT [cus_id] ,[cus_lname] ,[cus_fname] ,[cus_phone] ,[cus_address] ,[cus_city] ,[cus_state] ,[cus_zip] ,[cus_package_key] ,[cus_start_date_key] ,[cus_end_date_key] ,[cus_date_str] ,[cus_qtr_key] FROM [FACT].[CUSTOMERS_2011004] UNION ALL SELECT [cus_id] ,[cus_lname] ,[cus_fname] ,[cus_phone] ,[cus_address] ,[cus_city] ,[cus_state] ,[cus_zip] ,[cus_package_key] ,[cus_start_date_key] ,[cus_end_date_key] ,[cus_date_str] ,[cus_qtr_key] FROM [FACT].[CUSTOMERS_2012001] UNION ALL SELECT [cus_id] ,[cus_lname] ,[cus_fname] ,[cus_phone] ,[cus_address] ,[cus_city] ,[cus_state] ,[cus_zip] ,[cus_package_key] ,[cus_start_date_key] ,[cus_end_date_key] ,[cus_date_str] ,[cus_qtr_key] FROM [FACT].[CUSTOMERS_2012002] UNION ALL SELECT [cus_id] ,[cus_lname] ,[cus_fname] ,[cus_phone] ,[cus_address] ,[cus_city] ,[cus_state] ,[cus_zip] ,[cus_package_key] ,[cus_start_date_key] ,[cus_end_date_key] ,[cus_date_str] ,[cus_qtr_key] FROM [FACT].[CUSTOMERS_2012003] UNION ALL SELECT [cus_id] ,[cus_lname] ,[cus_fname] ,[cus_phone] ,[cus_address] ,[cus_city] ,[cus_state] ,[cus_zip] ,[cus_package_key] ,[cus_start_date_key] ,[cus_end_date_key] ,[cus_date_str] ,[cus_qtr_key] FROM [FACT].[CUSTOMERS_2012004] '); drop table [FACT].[CUSTOMERS_2011001]; alter database [BBQ_PART_VIEW] remove file FN_PART_VIEW_2011001; alter database [BBQ_PART_VIEW] remove filegroup FG_PART_VIEW_2011001;