Write a script that uses a cursor and dynamic SQL to output

Write a script that uses a cursor and dynamic SQL to output one row from each base user table in the AP database. Specifically exclude the tables named \"dtproperties\" and sysdiagrams\" from the result set.

Solution

DECLARE CUR_Table CURSOR FOR
SELECT name FROM sys.objects WHERE TYPE = \'U\' AND name NOT IN (\'dtproperties\',\'sysdiagrams\')

DECLARE @TableName VARCHAR(100)
DECLARE @Sql VARCHAR(8000)


OPEN CUR_Table

FETCH NEXT FROM CUR_Table INTO @TableName

WHILE @@fetch_status=0
BEGIN
    SET @Sql = \'SELECT TOP 1 * FROM \' + @TableName
    EXEC (@Sql)
    
    FETCH NEXT FROM CUR_Table INTO @TableName
    
END

CLOSE CUR_Table
DEALLOCATE CUR_Table