Last active
October 8, 2018 11:30
-
-
Save graywolfcorp/ca36ec91b1ca7acdc7f34780372284db to your computer and use it in GitHub Desktop.
spMSforeachtable
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE proc [dbo].[sp_MSforeachtable] | |
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, | |
@command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null, | |
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null | |
AS | |
declare @mscat nvarchar(12) | |
select @mscat = ltrim(str(convert(int, 0x0002))) | |
if (@precommand is not null) | |
exec(@precommand) | |
exec(N'declare hCForEachTable cursor global for select ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id = syso.object_id ' | |
+ N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 ' | |
+ @whereand) | |
declare @retval int | |
select @retval = @@error | |
if (@retval = 0) | |
exec @retval = dbo.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 | |
if (@retval = 0 and @postcommand is not null) | |
exec(@postcommand) | |
return @retval | |
GO | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment