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’

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();
}

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’