Wednesday, October 21, 2009

SQL Script to find total records of all tables.

This T-SQL script will return number of rows for each table in a database and total records across all tables. This will be very much useful for SQL admin to watch the data growth in database.

SELECT Row_Number() over (Order by sysindx.rowcnt DESC ) AS SlNo

,sysObj.name AS ‘Table Name’,sysindx.rowcnt AS ‘Row Count’

FROM sysobjects sysObj

inner join sysindexes sysindx

on sysindx.id = sysObj.id

WHERE sysindx.indid IN(0,1)

AND sysObj.xtype = ‘u’

AND sysObj.name NOT IN(’sysdiagrams’)

COMPUTE SUM(sysindx.rowcnt);



Note: This will work only on MSSQL 2005 and later. Because Compute and Row_Number were introduced new in 2005.

Undocumented SQL pwdcompare function.

There will be some occasion, where we need to check SQL authentication programmatically, here is one SQL undocumented function pwdComapare, will return ‘1′ if password match with password hash in SQL server.


This SQL stuff will explains rest.

Declare @loginName as varchar(100)

Declare @Password as varchar(50)

set @loginname = ‘guna’

set @Password = ‘gunapwd123′

IF (select pwdcompare(@Password,Password_hash) from sys.sql_logins

where name = @loginName) = 1

Select ‘Success’

ELse

Select ‘Failure’

Friday, October 16, 2009

Opening Windows Image Viewer programmatically - C#.NET

Below Code open windows defalut Image and photo viewer from .Net

public void OpenImageFile(string FilePath)
{
Process objProcess = new Process();
objProcess.StartInfo.FileName = “rundll32.exe”;
objProcess.StartInfo.UseShellExecute = false;
objProcess.StartInfo.Arguments = ” shimgvw.dll ImageView_Fullscreen ” + FilePath;
objProcess.Start();
objProcess.WaitForExit();
objProcess.Close();
}

Thursday, October 15, 2009

Warn update without WHERE clause

Create below SQL Trigger for table need to have this check.
T-SQl Script:
CREATE TRIGGER [dbo].[trgWarnUpdate]
ON [dbo].[Employee]
FOR UPDATE AS
BEGIN
DECLARE @Count int
SET @Count = @@ROWCOUNT;
IF @Count >= (SELECT top 1 rows
FROM sysindexes where name like 'pk%'
and id = object_id('Employee'))
BEGIN
RAISERROR('Updating all records restricted. To update all records disable this trigger.',16,1)
ROLLBACK TRANSACTION
RETURN;
END
END
GO


Note:Rename Emplyoee with respective SQL table. Assuming table has primary key and same get checked.

SQL Script:No of days in a month.

Below T-SQL script return no. of days for given date.

Declare @InputDate as datetime

Set @InputDate = ‘15-oct-2009′
Set @InputDate = cast(cast(month(@Inputdate) as char) + ‘-01-’ + cast(year(@InputDate) as char) as datetime)Select day(dateadd(d,-1,dateadd(M,1,@InputDate))) as ‘Days’