Database Knowledge Base / MS SQL Server /
Add comment
Name:
Email:
* Comment:
(Use BBcode - No HTML)
code
* Confirmation code:   Write the characters in the image above exactly as you see it


How to change the owner of all tables in a database?

DECLARE @oldowner sysname, @newowner sysname, @sql varchar(1000)

SELECT
  @oldowner = 'OLD_OWNER',
  @newowner = 'dbo',
  @sql = '
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
      AND TABLE_SCHEMA = ''' + @oldowner + '''
  )
  EXECUTE sp_changeobjectowner ''?'', ''' + @newowner + ''''

EXECUTE sp_MSforeachtable @sql




RSS