| MSSQL (6) |
| 1 |
I can not connect to my database through Enterprise Manager |
| 2 |
FTP through a stored procedure |
| 3 |
Change ownership of a table through SQL |
| |
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = 'oldOwner_CHANGE_THIS'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
EXECUTE sp_MSforeachtable @sql
-------------------------------
The same can be done to stored procedures:
-------------------------------
DECLARE @oldOwner sysname, @newOwner sysname
SELECT
@oldOwner = 'oldOwner_CHANGE_THIS'
, @newOwner = 'dbo'
select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE = 'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = 'oldOwner'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM information_schema.routines
WHERE
QUOTENAME(ROUTINE_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) = ''?''
AND ROUTINE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
EXECUTE sp_MSforeachtable @sql |
| 4 |
How do a write a stored procedure? |
| 5 |
Is there a simple way to edit MSAccess databases without having to download, edit, then reupload it? |
| 6 |
How do I make an encrypted connection to the mssql 2005 server through sql studio express? |
