Modify the owner SQL of a database table

Use Database

Go

Exec sp_changeobjectowner 'original table owner. Table name', Current Owner

For example

Use [sq8wecanwecancom]
Go
/***** Object: Table [DBO]. [TEMPLATE] script Date: 11/30/2010 15:04:43 ******/

Exec sp_changeobjectowner 'dbo. newsclass ', 'sq8wecancancom'

Run F5.

Note:

Sp_changeobjectowner: Change the owner of objects in the current database.
Syntax
Sp_changeobjectowner [@ objname =] 'object', [@ newowner =] 'owner'
Parameters
[@ Objname =] 'object'
Name of the existing table, view, or stored procedure in the current database. The object data type is nvarchar (517), and no default value is available. The object can be defined by the existing object owner. The format is existing_owner.object.
[@ Newowner =] 'owner'
Name of the security account of the new owner of the object. The owner's data type is sysname, and there is no default value. The owner must be a valid Microsoft SQL Server user or role in the current database or a Microsoft Windows NT & reg; user or group. When specifying a Windows NT user or group, specify the name known to the Windows NT user or group in the database (use sp_grantdbaccess to add ).

Example:

Exec sp_changeobjectowner 'sq8zhukang. web_ad ', 'dbo'

Change all table owners

Exec sp_msforeachtable 'exec sp_changeobjectowner ''? '', ''Dbo '''

An error may occur. Ignore it.

Note:

1) Description
System stored procedures sp_msforeachtable and sp_msforeachdb are two undisclosed stored procedures provided by Microsoft, starting from ms SQL 6.5.
Stored in the master database of SQL Server.

2) parameter description:
@ Command1 nvarchar (2000), -- the first running SQL command
@ Replacechar nchar (1) = n '? ', -- The specified placeholder.
@ Command2 nvarchar (2000) = NULL, -- the second running SQL command
@ Command3 nvarchar (2000) = NULL, -- the third running SQL command
@ Whereand nvarchar (2000) = NULL, -- an optional condition to select a table
@ Precommand nvarchar (2000) = NULL, -- the operation before the command is executed (similar to the operation before the control is triggered)
@ Postcommand nvarchar (2000) = NULL -- the operation after the command is executed (similar to the operation after the control is triggered)

3) Example
-- details of each table in the statistics database
exec sp_msforeachtable @ command1 = "sp_spaceused '? '"
-- get the number and capacity of records for each table:
exec sp_msforeachtable @ command1 =" print '? '",
@ command2 =" sp_spaceused '? '",
@ command3 =" select count (*) from? "
-- get the storage space of all databases:
exec sp_msforeachdb @ command1 =" print '? '",
@ command2 =" sp_spaceused "
-- check all databases
exec sp_msforeachdb @ command1 =" print '? '",
@ command2 =" DBCC checkdb (?) "
-- update statistics of all tables starting with T in the pubs Database:
exec sp_msforeachtable @ whereand =" and name like't % '",
@ replacechar = '*',
@ precommand = "Print 'updating statistics ..... 'print ''' ",
@ command1 =" print '* 'Update Statistics * ",
@ postcommand =" Print ''' complete update statistics! '"
-- delete data from all tables in the current database
sp_msforeachtable @ command1 = 'delete from? '
sp_msforeachtable @ command1 = "truncate table? "

4) parameter @ whereand usage
@ Whereand the parameter plays a role as a command condition restriction in the stored procedure. The specific statement is as follows:
@ Whereend, you can write @ whereand = 'and O. Name in (''table1', ''table2 '',.......)'
For example, I want to update the null value of the note column in Table1/Table2/table3.
Sp_msforeachtable @ command1 = 'Update? Set note = ''' where note is null', @ whereand = 'and O. name in (''table1', ''table2', ''table3 '')'

5 )"? "The special usage of stored procedures has created these two powerful stored procedures
Here "? "Is equivalent to the wildcard in the doscommand and when we search for files in windows.