April 1, 2012 at 5:09 pm
im having some issues with the scipt below. i would like for it to cycle through every table in each database and change any table name with schema name test2.please help
Declare @vusername varchar(50)
Set @vusername='test2'
DECLARE @cmd nvarchar(2000)
declare @table sysname
declare @owner nvarchar(255)
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
declare tabcurs cursor for
select table_name from INFORMATION_SCHEMA.TABLES where table_schema <>'dbo'
open tabcurs
fetch next from tabcurs into @table
while @@fetch_status=0
begin
set @owner = (select (table_schema) from INFORMATION_SCHEMA.TABLES where table_schema <>'dbo' and table_name = @table)
set @table = (select (table_name) from INFORMATION_SCHEMA.TABLES where table_schema <>'dbo' and table_name = @table)
END
DECLARE UserCurs CURSOR FOR
SELECT 'if exists (Select * from '+name+'..sysusers where name='''+@vusername+''') ' +
'Begin print ''exist '+name+''' Use ' +name +
' If exists
(
Select * from sysobjects
where uid in
(
Select uid from sysusers
where name='''+@vusername+'''
)
)'
--+'ALTER AUTHORIZATION ON SCHEMA::'+@vusername+' TO dbo'
--+'exec sp_changeobjectowner N''[' +@vusername + '].'+@table + ''', N''dbo'''
--+'exec sp_MSforeachtable @command1='sp_changeobjectowner '?','dbo'''
+'sp_msforeachtable 'sp_changeobjectowner '''+@vusername+''', 'dbo'''
+' exec sp_dropuser '''+@vusername+''' print ''the user '+@vusername+' has been removed from '+name+''' end else print '''+@vusername+' doesnt exist in '+name+''''
FROM
master..sysdatabases d
OPEN UserCurs
FETCH NEXT FROM UserCurs
INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @cmd
EXEC sp_executesql @cmd
FETCH NEXT FROM UserCurs
INTO @cmd
END
close tabcurs
CLOSE UserCurs
DEALLOCATE UserCurs
deallocate tabcurs
if exists (Select * from master..syslogins where name=@vusername)
Begin
exec sp_droplogin @vusername
print '''the sql login '+@vusername+' has been removed'
End
else
print 'the sql login '+@vusername+' doesnt exist'
April 1, 2012 at 8:12 pm
Is this SQL Server 2000 or 2005? You posted in 2000. Also, you didn't mention what problem you're having, nor what you expect this to change the schema to.
April 1, 2012 at 8:24 pm
the script is for sql 2000, and the issue is the script will only run on the selected database and doesnt update the table owner from test2 to dbo. i would like for the script to cycle thru all tables in each database and update schema owner test2 to dbo
April 2, 2012 at 7:08 am
Alter authorization is a SQL 2005+ command. It didn't exist in SQL Server 2000. If you print out the values and try to run them, you'll see an error.
http://msdn.microsoft.com/en-us/library/aa299742%28v=sql.80%29.aspx
Schema's didn't exist in SQL 2000 as we know them today. There were object owners, and if you want to change those, you need to use sp_changeobjectowner (http://msdn.microsoft.com/en-us/library/aa259618%28v=sql.80%29.aspx)
Look through all tables, and execute that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply