Getting all the primary key and foreign keys in a given database
select OBJECT_NAME(PARENT_OBJ) TABLE_NAME, CASE WHEN XTYPE ='F' THEN 'FORIEGN KEY' ELSE 'PRIMARY KEY' END KEY_TYPE , NAME KEY_NAME from sysobjects where Xtype in ('F' , 'pK') ORDER BY XTYPE DESC
This script can be used to get all the primary key and foreign key relationships within a given database.It sometimes can be helpful on a project with a complex database having hundreds and thousands of relationships and in such a database if you have to perform some major changes so you can have a quick look and you can know which all tables and relationship will be effected by these changes.
2. Delete All the Stored Procs for a particular database
Alter Procedure dbo.DeleteAllProcedures As declare @procName varchar(500) declare cur cursor for select [name] from sys.objects where type = 'p' open cur fetch next from cur into @procName while @@fetch_status = 0 begin if @procName <> 'DeleteAllProcedures' exec('drop procedure ' + @procName) fetch next from cur into @procName end close cur deallocate cur Go Grant Execute On dbo.DeleteAllProcedures To Public Go
This can only be handy when you want to delete all the user defined stored procs for a particular database
3.Change the default owner of a stored proc
USE YourDataBaseName GO DECLARE @OldOwner sysname, @NewOwner sysname SET @OldOwner = 'dbo' SET @NewOwner = 'dev' DECLARE CURS CURSOR FOR SELECT name FROM sysobjects WHERE type = 'p' AND uid = (SELECT uid FROM sysusers WHERE name = @OldOwner) AND NOT name LIKE 'dt%' FOR READ ONLY DECLARE @ProcName sysname OPEN CURS FETCH CURS INTO @ProcName WHILE @@FETCH_STATUS = 0 BEGIN IF @@VERSION >= 'Microsoft SQL Server 2005' BEGIN EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName) exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner') END ELSE EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''') FETCH CURS INTO @ProcName END CLOSE CURS DEALLOCATE CURS
4.Finding any stored proc containing the text of your choice
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%yoursearchtext%' AND ROUTINE_TYPE='PROCEDURE'
5.Reducing the size of your transaction log
dbcc shrinkfile (mydatabasename_log, 10);
This command will reduce the size of your database transaction log to 10MB. But if you are on production then you should not use this method to shrink your database, you should avoid shrinking the database without finding the probable reason why is it growing so fast.