USE master
GO
IF OBJECT_ID('sp_CursorScript') IS NULL EXEC ('CREATE PROCEDURE sp_CursorScript AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_CursorScript
@ObjectName SYSNAME = NULL, /*Fully qualified name*/
@PrintSQL BIT = 1,
@FilePath VARCHAR(4000) = NULL /* Will write the generated sql to the path */
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @SQL VARCHAR(MAX),
@OBJECT_ID INT = OBJECT_ID(Util.dbo.TrimBothEnds(@ObjectName))
IF @OBJECT_ID IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_CursorScript',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END ;
WITH c1
AS (SELECT TOP 999999
sc.name AS SchemaName,
tb.name AS objectname,
QUOTENAME(sc.name) + '.' + QUOTENAME(tb.name) AS objectfullname,
co.name AS ColumnName,
ct.ColumnType AS ColDef
FROM sys.schemas AS sc (NOLOCK)
INNER JOIN sys.objects AS tb (NOLOCK) ON sc.SCHEMA_ID = tb.SCHEMA_ID
INNER JOIN sys.columns AS co (NOLOCK) ON co.OBJECT_ID = tb.OBJECT_ID
INNER JOIN sys.types AS ts (NOLOCK) ON ts.user_type_id = co.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(ts.name, co.max_length, co.PRECISION, co.scale, co.collation_name, db.collation_name) ct
WHERE tb.OBJECT_ID = @OBJECT_ID
ORDER BY column_id)
SELECT @SQL = 'DECLARE ' + Util.dbo.StringConcat('@' + ColumnName + ' ' + coldef, ', ') + ',
@RowNum int = 0
DECLARE ' + objectname + '_cursor CURSOR FAST_FORWARD FOR
SELECT TOP 10 ' + Util.dbo.StringConcat(ColumnName, ', ') + ' FROM ' + objectfullname + ' (NOLOCK)
OPEN ' + objectname + '_cursor
FETCH NEXT FROM ' + objectname + '_cursor INTO ' + Util.dbo.StringConcat('@' + ColumnName, ', ') + '
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RowNum = @RowNum + 1
SELECT @RowNum AS RowNum, ' + Util.dbo.StringConcat('@' + ColumnName + ' AS ' + ColumnName, ', ') + '
FETCH NEXT FROM ' + objectname + '_cursor INTO ' + Util.dbo.StringConcat('@' + ColumnName, ', ') + '
END
CLOSE ' + objectname + '_cursor
DEALLOCATE ' + objectname + '_cursor'
FROM c1
GROUP BY objectname,
SchemaName,
objectfullname
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
GO
EXEC sys.sp_ms_marksystemobject sp_CursorScript
GO