Query to get the latest FULL backup details

 

SELECT   cast ((backup_finish_date - backup_start_date) AS TIME(0)) as DURATION, SERVER_NAME,b.DATABASE_NAME,[TYPE],backup_set_id,     b.backup_start_date,b.backup_finish_date, Round(b.backup_size/(1024*1024),2) as Size_n_MB, Physical_device_name   FROM msdb.dbo.backupset b,   ( select a.DATABASE_NAME, MAX(a.backup_set_id) id   from msdb.dbo.backupset a  WHERE a.TYPE = 'D' and  a.DATABASE_NAME = DATABASE_NAME  group by a.DATABASE_NAME ) cc,   msdb.dbo.backupmediafamily f WHERE TYPE = 'D' AND b.backup_set_id = cc.id   AND b.media_set_id = f.media_set_id

Find time difference between two dates

 

SELECT b.backup_finish_date, b.backup_start_date, CAST ((b.backup_finish_date - b.backup_start_date) AS TIME(0)) FROM backupset b WHERE convert(varchar(12),b.backup_finish_date,101) = '10/13/2013'

SQL Server: Query to check if given column exists

 

select s.[name] 'Schema', t.[name] 'Table', c.[name] 'Column', d.[name] 'Data Type', d.[max_length] 'Max Length', d.[precision] 'Precision', c.[is_identity] 'Is Id', c.[is_nullable] 'Is Nullable', c.[is_computed] 'Is Computed', d.[is_user_defined] 'Is UserDefined', t.[modify_date] 'Date Modified', t.[create_date] 'Date created' from sys.schemas s inner join sys.tables t on s.schema_id = t.schema_id inner join sys.columns c on t.object_id = c.object_id inner join sys.types d on c.user_type_id = d.user_type_id where c.name like 'Person%'

How to: search for a word or column name across the Procedure, Function, Trigger, View

 

DECLARE @SearchPhrase nvarchar(1000)   SET @SearchPhrase = N'backup'   -- Place your search string   SELECT DISTINCT sysobjects.name AS [Object Name], CASE   WHEN sysobjects.xtype = 'P' THEN 'Stored Proc'   WHEN sysobjects.xtype = 'TF' THEN 'Function'   WHEN sysobjects.xtype = 'TR' THEN 'Trigger'   WHEN sysobjects.xtype = 'V' THEN 'View' END as [Object Type], (SELECT ParentTable.[Name] FROM sysobjects ParentTable WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable FROM sysobjects,      syscomments WHERE sysobjects.id = syscomments.id AND sysobjects.type in ('P','TF','TR', 'V') --AND sysobjects.category = 0  --- To restrict only user defined object AND CHARINDEX(@SearchPhrase, syscomments.text) > 0

How to: find when the database last got restored?

 

   1:  SELECT destination_database_name,max(restore_date) as restore_date

   2:  FROM msdb.dbo.restorehistory 

   3:  WHERE destination_database_name = 'testDB’  ---- Place your database name here

   4:  GROUP BY destination_database_name

The result will look like;

destination_database_name     restore_date

testDB                        2013-04-29 11:49:19.940

Query to find the Linked Servers with given remote login

 

It would be useful to cross check, when the Linked Server remote login password has changed.
In this case, linked server will wail due to wrong password. To fix this we have reset the correct password.

SELECT s.server_id,S.name,S.product,S.provider,l.remote_name,'Remote_Login_Modified_Dt'=l.modify_date,s.modify_date FROM SYS.SERVERS S INNER JOIN SYS.LINKED_LOGINS L ON  s.server_id = L.server_id WHERE S.server_id > 0 AND S.product ='SQL Server' AND L.remote_name ='sa'

Returning the parameters for a specified stored procedure or function

 

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>; GO SELECT SCHEMA_NAME(schema_id) AS schema_name     ,o.name AS object_name     ,o.type_desc     ,p.parameter_id     ,p.name AS parameter_name     ,TYPE_NAME(p.user_type_id) AS parameter_type     ,p.max_length     ,p.precision     ,p.scale     ,p.is_output FROM sys.objects AS o INNER JOIN sys.parameters AS p ON o.object_id = p.object_id WHERE o.object_id = OBJECT_ID('<schema_name.object_name>') ORDER BY schema_name, o.object_name, p.parameter_id; GO

 

Reference: http://msdn.microsoft.com/en-us/library/ms190324.aspx

List database objects modified in the last ‘N’ days

 

Before you run the following query, replace <database_name> and <n_days> with valid values.

 

USE <database_name>; GO SELECT name AS object_name   ,SCHEMA_NAME(schema_id) AS schema_name   ,type_desc   ,create_date   ,modify_date FROM sys.objects WHERE modify_date > GETDATE() - <n_days> ORDER BY modify_date; GO

 

Source: http://msdn.microsoft.com/en-us/library/ms190324.aspx

Schema in SQL Server

 
Users and schemas are completely separate. The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users;A schema is simply a container of objects. Schemas own objects and principles own schemas.

1. A single schema can contain objects owned by multiple database users.
2. A database user can be dropped without dropping objects in a corresponding schema

A schema can be owned by any user, and its ownership is transferable.

Why Schema introduced on SQL Server?

The Users and Schemas separation means objects and schemas can be created before users are added to the database. It also means a user can be dropped without specifically dropping the objects owned by that user. A schema can only be owned by one user at a time, but a single user can simultaneously own many schemas.

How to transfer Schema ownership?

The following example transfers ownership of the schema LabProduction to user Sivaprasad.

ALTER AUTHORIZATION ON SCHEMA::LabProduction TO Sivaprasad;
GO

Changes the ownership of a securable – using ALTER AUTHORIZATION

ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals

How to move objects between Schema?

ALTER SCHEMA can only be used to move securables between schemas in the same database. To change or drop a securable within a schema, use the ALTER or DROP statement specific to that securable.

For example, below modifies the schema HumanResources by transferring the table Address from schema Person into the schema

ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO

-- Create TestDB Database Create database TestDB go -- Change the DB Context to TestDB Use TestDB go -- Created Schema Student Create Schema Student go --– Created table in Student schema – Create Table Student.Student_Info ( Student_No int Primary Key identity(1,1), Student_Name varchar(50) ) go --– Insert Data Insert Into Student.Student_Info Values('Sivaprasad') go --– Select Data Select * From Student.Student_Info go --– Create another schema Course Create Schema Course go --– Transfer Objects from Student Schema to Course Schema ALTER SCHEMA Course TRANSFER Student.Student_Info go --– Assign Permission to Schema GRANT SELECT ON SCHEMA::Course TO Sivaprasad go

TSQL Query to find the DB Maintenance status

 

Are you curious to find the Database Backup / Restore status?
Do you have to find out the status the DBCC CHECKDB ?
Do you have to report when the ROLLBACK would finish ?
Do you want judge how long the Database Shrink will take?

Here is the answer!

The query listed below can be used to find the Percentage of work completed for the following commands:

1. ALTER INDEX REORGANIZE
2. AUTO_SHRINK option with ALTER DATABASE
3. BACKUP DATABASE
4. DBCC CHECKDB
5. DBCC CHECKFILEGROUP
6. DBCC CHECKTABLE
7. DBCC INDEXDEFRAG
8. DBCC SHRINKDATABASE
9. DBCC SHRINKFILE
10. RECOVERY
11. RESTORE DATABASE,
12. ROLLBACK
13. TDE ENCRYPTION

We have to replace the where condition filter based on the requirement.

SELECT command,             sqltext.text,             start_time,             percent_complete,             CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '                   + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '                   + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,             CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '                   + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '                   + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,             dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests Req CROSS APPLY sys.dm_exec_sql_text(Req.sql_handle) sqltext WHERE req.command in ('DBCC CHECKDB')

Processing order of SELECT Statement

 

The following steps show the processing order for a SELECT statement.

1. FROM

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. WITH CUBE or WITH ROLLUP

7. HAVING

8. SELECT

9. DISTINCT

10. ORDER BY

11. TOP

This is the reason where we can not use column alias on Where clause, however we can use it on Select / Order by.

If interested on the logical order of the query processing poster, download it from here.

Reference: http://msdn.microsoft.com/en-us/library/ms189499.aspx

SQL Server 2005/ 2008 Query to find when the database last used

Below is the TSQL Query to find when the database last accessed for SQL Server 2005 / 2008

-- Query to find when the database last accessed on SQL Server 2005 / 2008 --- select d.name, x1 = (select X1= max(bb.xx) from (     select xx = max(last_user_seek)         where max(last_user_seek) is not null     union all     select xx = max(last_user_scan)         where max(last_user_scan) is not null     union all     select xx = max(last_user_lookup)         where max(last_user_lookup) is not null     union all         select xx = max(last_user_update)         where max(last_user_update) is not null) bb) FROM master.dbo.sysdatabases d left outer join sys.dm_db_index_usage_stats s on d.dbid= s.database_id group by d.name

 

 

 

Format Date or Time without “/”

Format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement	Output
select replace(convert(varchar, getdate(),111),'/','')	20100413
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')	20100413 004426
Select cast(int, convert(char,getdate(),111))	20100413

How to change the owner of a maintenance plan

Scenario 1:
-------------
One of our members of staff had left and we had the usual case of a few jobs failing with:

"Unable to determine if the owner (DOMAIN\xxx) of job has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))."

Scenario 2:
------------
A job for running a nightly transaction log maintenance plan was created and owned by an account that was a member of the Domain Admins group. The account was subsequently removed from the Domain Admins group and the job failed (owner did not have server access), as expected.

The job owner was changed to another account that is a member of the Domain Admins group, and the job ran successfully for seven days. On the eighth day, another user, also a member of Domain Admins group, edited the maintenance plan to add a database. After the maintenance plan was saved, job ownership reverted to the original job creator (no longer a Domain Admin) and the job failed on its next scheduled run.

/*Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers

UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'

For SQL Server 2008:

update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] = 'MaintenancePlan'