sql2k非常用重要操作

刺骨的言语ヽ痛彻心扉 2022-08-27 10:39 113阅读 0赞

sql2k非常用操作

1.更改物件的擁有者

方法1.使用sp\_changeobjectowner修改單個物件  
exec sp\_changeobjectowner '要改的表名','dbo'

方法2.自定義存儲過程ChangeObjectOwner修改單個擁有者  
CREATE PROCEDURE dbo.ChangeObjectOwner  
@OldOwner as NVARCHAR(128),--参数原所有者  
@NewOwner as NVARCHAR(128)--参数新所有者  
AS

DECLARE @Name as NVARCHAR(128)  
DECLARE @Owner as NVARCHAR(128)  
DECLARE @OwnerName as NVARCHAR(128)

DECLARE curObject CURSOR FOR  
select 'Name' = name,  
'Owner' = user\_name(uid)  
from sysobjects  
where user\_name(uid)=@OldOwner  
order by name

OPEN curObject  
FETCH NEXT FROM curObject INTO @Name, @Owner  
WHILE(@@FETCH\_STATUS=0)  
BEGIN  
if @Owner=@OldOwner  
begin  
set @OwnerName = @OldOwner + '.' + rtrim(@Name)  
exec sp\_changeobjectowner @OwnerName, @NewOwner  
end

FETCH NEXT FROM curObject INTO @Name, @Owner  
END

close curObject  
deallocate curObject  
GO

方法3.使用sp\_MSforeachtable修改所有表的擁有者  
這是一個未公開的存儲過程.在master中,其源代碼如下:  
create proc 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  
/\* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set \*/  
/\* @precommand and @postcommand may be used to force a single result set via a temp table. \*/

/\* Preprocessor won't replace within quotes so have to use str(). \*/  
declare @mscat nvarchar(12)  
select @mscat = ltrim(str(convert(int, 0x0002)))

if (@precommand is not null)  
exec(@precommand)

/\* Create the select \*/  
exec(N'declare hCForEach cursor global for select ''\['' + REPLACE(user\_name(uid), N''\]'', N''\]\]'') + ''\]'' + ''.'' + ''\['' + REPLACE(object\_name(id), N''\]'', N''\]\]'') + ''\]'' from dbo.sysobjects o '  
\+ 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 = sp\_MSforeach\_worker @command1, @replacechar, @command2, @command3

if (@retval = 0 and @postcommand is not null)  
exec(@postcommand)

return @retval

調用:  
EXEC sp\_MSforeachtable 'exec sp\_changeobjectowner ''?'',''dbo'' '

2.查找所有表結構

CREATE PROCEDURE \[dbo\].\[spGetUserTable\] AS  
\--取得用戶表結构信息  
BEGIN  
SELECT  
表名=case when a.colorder=1 then d.name else '' end,  
字段序号=a.colorder,  
字段名=a.name,  
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,  
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (  
SELECT name FROM sysindexes WHERE indid in(  
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid  
))) then '√' else '' end,  
类型=b.name,  
占用字节数=a.length,  
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),  
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),  
允许空=case when a.isnullable=1 then '√'else '' end,  
默认值=isnull(e.text,''),  
字段说明=isnull(g.\[value\],'')  
FROM syscolumns a  
left join systypes b on a.xtype=b.xusertype  
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'  
left join syscomments e on a.cdefault=e.id  
left join sysproperties g on a.id=g.id and a.colid=g.smallid  
order by a.id,a.colorder  
END  
GO