1日1時T-SQL:ストアド・プロシージャ・テキストのエクスポート
- DECLARE @ServerName NVARCHAR(256);
- DECLARE @UserName NVARCHAR(256);
- DECLARE @Password NVARCHAR(256);
- DECLARE @DatabaseName NVARCHAR(256);
- DECLARE @ExecString NVARCHAR(1024);
-
- SET @ServerName = N'ServerName\InstenceName';
- SET @UserName = N'UserName';
- SET @Password = N'Password';
- SET @DatabaseName = N'DatabaseName';
-
- SELECT
- ROW_NUMBER()
- OVER(ORDER BY B.[name], A.[name] ASC, A.[modify_date] DESC)
- AS [ID],
- A.[object_id] AS [ObjectID],
- B.[name] AS [SchemaName],
- A.[name] AS [ProcedureName],
- N'[' + B.[name] + N'].[' + A.[name] + N']' AS [FullProcedureName],
- A.[create_date] AS [CreateDate],
- A.[modify_date] AS [ModifyDate],
- A.[type] AS [Type],
- A.[type_desc] AS [TypeDescription]
- INTO
- #Temp
- FROM
- sys.procedures AS A
- RIGHT JOIN
- sys.schemas AS B
- ON
- A.[schema_id] = B.[schema_id]
- WHERE
- A.[object_id] IS NOT NULL
- ORDER BY
- B.[name], A.[name] ASC, A.[modify_date] DESC
-
- DECLARE @ID INT;
- DECLARE @SchemaName NVARCHAR(128);
- DECLARE @ProcedureName NVARCHAR(512);
- DECLARE @ModifyDate CHAR(8);
-
- DECLARE contact_cursor CURSOR FOR
- SELECT
- [ID],
- [SchemaName],
- [ProcedureName],
- CONVERT(CHAR(8), [ModifyDate], 112) AS [FlagModifyDate]
- FROM #Temp
-
- OPEN contact_cursor
-
- -- Perform the first fetch.
- FETCH NEXT FROM contact_cursor
- INTO
- @ID,
- @SchemaName,
- @ProcedureName,
- @ModifyDate
-
- -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
- WHILE @@FETCH_STATUS = 0
- BEGIN
-
- SET @ExecString =
- N'sqlcmd -S ' + @ServerName +
- N' -U ' + @UserName +
- N' -P ' + @Password +
- N' -d ' + @DatabaseName +
- N' -Q "exec sp_helptext ''' + @SchemaName + N'.' + @ProcedureName +
- N'''" -o D:\test\' + CAST(@ID AS NVARCHAR(64)) + '_' + @SchemaName + N'.' + @ProcedureName + N'_' + @ModifyDate + N'.sql'
-
- PRINT @ExecString
- EXEC xp_cmdshell @ExecString, no_output
-
- FETCH NEXT FROM contact_cursor
- INTO
- @ID,
- @SchemaName,
- @ProcedureName,
- @ModifyDate
- END
-
- CLOSE contact_cursor
- DEALLOCATE contact_cursor
- GO
-
- DROP TABLE #Temp
- GO
本文は“Fast”のブログから出て、転載をお断りします!