How to view MSSQL 2005 logins

hello all,
use this to view your 2005 server logins


SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'


Enjoy

Itay

How to view backup history

Hello all,

I found this little script that helped me to view all my backup history
enjoy

SELECT
sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576)
as 'Size Meg' ,
cast((bkup.backup_size /1073741824) as decimal (9,2))
as 'Gig',
server_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as
decimal (8,3))/60 as
decimal (8,1))
as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name
= sysdb.name
where backup_finish_date >
DATEADD(DAY, -60, (getdate()))
ORDER BY sysdb.name, bkup.backup_finish_date desc


thanks to homebrew01

itay

Find the next running schedule job

This is a little script I just found and wanted to share.


USE msdb;
WITH CTE AS (SELECT schedule_id,
job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
FROM sysjobschedules)
SELECT A.name Job_Name,
'Will be running today at '+SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12
ELSE
SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)
'Scheduled At'
FROM sysjobs A , CTE B
WHERE A.job_id = B.job_id
AND
SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101)
AND
(SUBSTRING( CONVERT(VARCHAR(10),CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)


Thanks to http://mssqlonline.blogspot.com/

enjoy

itay

The Power of GO

Hello all.

Nice little tip for you all.
If you don't want to use complicated loops, declare counters and so on you can use to power of the GO keyword to the rescue.
The GO keyword in SQL 2000, 2005 tells the Server to execute the preceding code as one batch
Use the GO in 2005 to actually tell the server how many times you would like the batch to execute.

For an example we will create a new table and insert new data 1000 times

CREATE TABLE
dbo.TempTable
(ID INT IDENTITY (1,1),
ROWID uniqueidentifier)
GO
INSERT INTO dbo. TempTable (ROWID)
VALUES (NEWID())
GO 1000


Enjoy and don’t forget to leave some comments
Itay

How to get a list of all Temp tables

Hello all.
One of my friends asked me today how he can get a list of all Temp tables.
Well the answer is very simple, that I have to share.
Just use this:


use tempdb
go
select *
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = USER and
TABLE_CATALOG = 'tempdb'
go

Enjoy and don’t forget to leave comments.
Itay.