How to recover SA password on Microsoft SQL Server 2008 R2

http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/

Pull Replication

http://www.sqlservercentral.com/articles/Stairway+Series/72322/

Rename a SQL Server database

 Note: If not done. Change user type muti-user to single user

This command works for SQL Server 2005, 2008, 2008R2 and 2012:

ALTER DATABASE oldName MODIFY NAME = newName

If you are using SQL Server 2000 you can use this T-SQL command to make the database name change.  This still works for SQL 2005, 2008, 2008R2 and 2012, but Microsoft says it will be phased out at some time.

EXEC sp_renamedb 'oldName', 'newName'
 

tsql : Split row based on row number , ROW_NUMBER(),RANK(),

select * from  (select
 F=cast((RowNo/100) as int)+1,F1,F2 from (
               SELECT ROW_NUMBER() OVER(ORDER BY f2) RowNo,F1,F2 FROM TempTable where F2 IS NOT NULL
               ) sub1) AS T WHERE T.F=1

Drop All Stored procedures,views and functions - GET Objects from sys.objects

 GET Objects from sys.objects
--select *from sys.objects where type in('P','V','FN') order by type


--Query for stored procedure
select ('DROP PROCEDURE ' + NAME),*from sys.objects where type in('P') order by type

--Query for view
select ('DROP VIEW' + NAME),*from sys.objects where type in('V') order by type

--Query for FUNCTIONS
select ('DROP FUNCTION' + NAME),*from sys.objects where type in('FN') order by type

Fixed the error: 15138 The database principal owns a schema in the database, and cannot be dropped. - Drop the USER sql server 2008 r2

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

Cause: That means, you are trying to drop a user owning a schema. In order to drop the user, you have to find the schema that’s assigned and then transfer the ownership to another user/role or to drop it.

Resolution: You can fix the issue following two ways.

Step 1 :
By script: You can find out which schema is owned by this user with the query below:

SELECT name FROM sys.schemas WHERE principal_id = USER_ID('myUser')
Step 2 :
Then, use the names found from the above query below in place of the SchemaName below. And drop your user.

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

GO
Step 3 :
DROP USER myUser

Find Stored Procedures and Functions to Refer any Tables

Hi,
Just replace the "search" in the code with the name of the table/view/stored procedure/function etc that you want to search on


select        distinct
            ObjectType        = o.type_desc
        ,    ObjectName        = o.name
        ,    CodeSequence    = c_display.colid
        ,    Code            = c_display.[text]
from        sys.objects o
inner join    sys.syscomments c_search
            on c_search.id = o.[object_id]
inner join    sys.syscomments c_display
            on c_display.id = o.[object_id]
where        o.type_desc not in ('INTERNAL_TABLE','SERVICE_QUEUE','SYSTEM_TABLE')
and            c_search.[text] like '%search%'
order by    1, 2, 3