Hi, there are some task given .Needed solution.

Thank you

use [PRODUCT DATABASE] go SELECT * FROM PRODUCTS_DATA

-- REQUIREMENT: HOW TO ACCESS 5TH ROW OF THE TABLE? -- EXAMPLE #1: DECLARE CRS CURSOR FOR SELECT * FROM PRODUCTS_DATA

OPEN CRS

FETCH NEXT FROM CRS FETCH NEXT FROM CRS FETCH NEXT FROM CRS FETCH NEXT FROM CRS FETCH NEXT FROM CRS

CLOSE CRS DEALLOCATE CRS

-- EXAMPLE #2: DECLARE CRS CURSOR global STATIC SCROLL FOR SELECT * FROM PRODUCTS_DATA

OPEN CRS

FETCH ABSOLUTE 5 FROM CRS FETCH LAST FROM CRS FETCH FIRST FROM CRS FETCH LAST FROM CRS FETCH PRIOR FROM CRS

CLOSE CRS DEALLOCATE CRS

-- EXAMPLE #3: -- REQUIREMENT: HOW TO REPORT LIST OF ALL TABLES AND RESPECTIVE ROW COUNT?

DECLARE CRS CURSOR GLOBAL DYNAMIC SCROLL FOR SELECT NAME FROM SYS.TABLES

OPEN CRS DECLARE @TABNAME SYSNAME, @QUERY NVARCHAR(MAX) FETCH FIRST FROM CRS INTO @TABNAME -- THIS IS TO FETCH 1ST ROW FROM THE CURSOR WHILE @@FETCH_STATUS = 0 -- AS LONG AS CURSOR HAS MORE ROWS TO FETCH BEGIN SET @QUERY = 'SELECT COUNT(*) FROM ' + @TABNAME EXEC SP_EXECUTESQL @QUERY FETCH NEXT FROM CRS INTO @TABNAME -- THIS IS TO FETCH NEXT ROW FROM THE CURSOR END CLOSE CRS DEALLOCATE CRS

-- EXAMPLE #4: -- REQUIREMENT: HOW TO REPORT LIST OF ALL TABLES AND RESPECTIVE ROW COUNT? DECLARE CRS CURSOR GLOBAL DYNAMIC SCROLL FOR SELECT NAME FROM SYS.TABLES

OPEN CRS DECLARE @TABNAME SYSNAME, @QUERY NVARCHAR(MAX) FETCH FIRST FROM CRS INTO @TABNAME -- THIS IS TO FETCH 1ST ROW FROM THE CURSOR WHILE @@FETCH_STATUS = 0 -- AS LONG AS CURSOR HAS MORE ROWS TO FETCH BEGIN SET @QUERY = 'SELECT COUNT(*) AS RCOUNT FROM ' + @TABNAME EXEC SP_EXECUTESQL @QUERY FETCH NEXT FROM CRS INTO @TABNAME -- THIS IS TO FETCH NEXT ROW FROM THE CURSOR END CLOSE CRS DEALLOCATE CRS

-- EXAMPLE #5: -- REQUIREMENT: HOW TO REPORT LIST OF ALL TABLES AND RESPECTIVE ROW COUNT? DECLARE CRS CURSOR GLOBAL DYNAMIC SCROLL FOR SELECT NAME FROM SYS.TABLES

OPEN CRS DECLARE @TABNAME SYSNAME, @QUERY NVARCHAR(MAX) FETCH FIRST FROM CRS INTO @TABNAME -- THIS IS TO FETCH 1ST ROW FROM THE CURSOR WHILE @@FETCH_STATUS = 0 -- AS LONG AS CURSOR HAS MORE ROWS TO FETCH BEGIN SET @QUERY = 'SELECT '''+ @TABNAME + ''' AS TABLENAME, COUNT(*) AS RCOUNT FROM ' + @TABNAME EXEC SP_EXECUTESQL @QUERY FETCH NEXT FROM CRS INTO @TABNAME -- THIS IS TO FETCH NEXT ROW FROM THE CURSOR END CLOSE CRS DEALLOCATE CRS

-- EXAMPLE #6: CREATE PROC USP_REPORT_DATA AS DECLARE CRS CURSOR GLOBAL DYNAMIC SCROLL FOR SELECT NAME FROM SYS.TABLES

OPEN CRS DECLARE @TABNAME SYSNAME, @QUERY NVARCHAR(MAX) FETCH FIRST FROM CRS INTO @TABNAME -- THIS IS TO FETCH 1ST ROW FROM THE CURSOR WHILE @@FETCH_STATUS = 0 -- AS LONG AS CURSOR HAS MORE ROWS TO FETCH BEGIN SET @QUERY = 'SELECT '''+ @TABNAME + ''' AS TABLENAME, COUNT(*) AS RCOUNT FROM ' + @TABNAME EXEC SP_EXECUTESQL @QUERY FETCH NEXT FROM CRS INTO @TABNAME -- THIS IS TO FETCH NEXT ROW FROM THE CURSOR END CLOSE CRS DEALLOCATE CRS

CREATE TABLE #TABLE (TNAME SYSNAME, RCOUNT BIGINT) INSERT INTO #TABLE EXEC USP_REPORT_DATA SELECT * FROM #TABLE

SELECT COUNT(*) AS RCOUNT FROM PRODUCTS_DATA SELECT 'PRODUCTS_DATA' AS TABLENAME, COUNT(*) AS RCOUNT FROM PRODUCTS_DATA SELECT '''PRODUCTS_DATA''' AS TABLENAME, COUNT(*) AS RCOUNT FROM PRODUCTS_DATA

-- FINAL SOLUTION: CREATE PROC USP_REPORTTABLES AS BEGIN DECLARE CRS CURSOR FOR SELECT NAME FROM SYS.TABLES DECLARE @TABNAME SYSNAME, @QUERY NVARCHAR(MAX) OPEN CRS FETCH NEXT FROM CRS INTO @TABNAME -- THIS IS TO FETCH FIRST ROW WHILE @@FETCH_STATUS = 0 -- LOOP; AS LONG AS CURSOR HAS MORE ROWS TO FETCH BEGIN SET @QUERY = 'SELECT '''+@TABNAME+''' AS TABLENAME, COUNT(*) AS RCOUNT FROM '+ @TABNAME EXEC SP_EXECUTESQL @QUERY -- TO DYNAMICALLY EXECUTE ANY GIVEN QUERY FETCH NEXT FROM CRS INTO @TABNAME -- THIS IS TO FETCH NEXT ROW END CLOSE CRS DEALLOCATE CRS END

EXEC USP_REPORTTABLES

CREATE TABLE #TABLE (TABNAME SYSNAME, RCOUNT BIGINT) INSERT INTO #TABLE EXEC USP_REPORTTABLES SELECT * FROM #TABLE

SELECT 'ABC' SELECT '''ABC''' USE PRODUCTDATABASE

SELECT 'TIME_DATA', COUNT(*) FROM TIME_DATA

-- TASK: HOW TO READ DATA FROM PRODUCTS TABLE AND RESULT IN THIS FORMAT: PRODUCTID PRODUCTNAME CLASS SIZE LISTPRICE ----------------------------------------------------------------- 101 ABC L S 1 102 ABCD L M 3 103 ABC DE L S 8

-- TASK: HOW TO REPORT EACH DATABASE FROM THE SERVER AND ITS MDF FILE LOCATION EXCEPT FOR SYSTEM DATBASES? DATABASENAME : MDF_FILE_PATH ---------------------------------