Total number of records of all tables in a MSSQL database

Total number of records of all tables in a MSSQL database


Run the below query agganist the database you want the result

SELECT      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)),QUOTENAME(sOBJ.name) AS [TableName]      , SUM(sdmvPTNS.row_count) AS [RowCount]FROM      sys.objects AS sOBJ      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS            ON sOBJ.object_id = sdmvPTNS.object_id
WHERE      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0      AND sdmvPTNS.index_id < 2GROUP BY
      sOBJ.schema_id
      , sOBJ.name      having SUM(sdmvPTNS.row_count)>0ORDER BY [TableName]
GO

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Pagging in sql

In one of my older post i posted about  paging in sql using Row_Number function (http://simplyasp.blogspot.in/2010/11/paging-records-using-sql-server-2005.html).on latest version of sql they had improved the paging functionality by bothe performance base and also in readbility base too. on 2008  they introduce CTE (Common table expression) and Derived table.  on sql 2012 they done a mass step on that FETCH NEXT functionality ... i love that.

Below i show all the method of paging i mentioned above.

--SQL 2005/2008 Paging Method Using Derived Table

DECLARE @Start INT

DECLARE @End INT

SELECT @Start = 14000,@End = 14050

 

SELECT LastName, FirstName, EmailAddress

FROM (SELECT LastName, FirstName, EmailAddress,

      ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber

      FROM Employee) EmployeePage

WHERE RowNumber > @Start AND RowNumber <= @End

ORDER BY LastName, FirstName, EmailAddress

GO

 

--SQL 2005/2008 Paging Method Using CTE

DECLARE @Start INT

DECLARE @End INT

SELECT @Start = 14000,@End = 14050;

 

WITH EmployeePage AS

(SELECT LastName, FirstName, EmailAddress,

 ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber

 FROM Employee)

SELECT LastName, FirstName, EmailAddress

FROM EmployeePage

WHERE RowNumber > @Start AND RowNumber <= @End

ORDER BY LastName, FirstName, EmailAddress

GO

 

--SQL SERVER 2012

SELECT LastName, FirstName, EmailAddress

FROM Employee

ORDER BY LastName, FirstName, EmailAddress

OFFSET 14000 ROWS

FETCH NEXT 50 ROWS ONLY;

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

SQL Function return SQL TABLE

using table values parameter we can achieve it

Function :

CREATE FUNCTION FunctionName
(
@id INT
)
returns table as
return
(
Select * from TableName WHERE id=@id
)

How to call:

select * from FunctionName(1)

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Sanitize database inputs

A lesson to all developers 

Sanitize database inputs

 


If u had any trouble just ask, Happy to help u :)
 Stay Tune... Have a nice day... 'N happy Coding :)

Passing store procedure result to another store procedure as parameter

Passing store procedure result to another store procedure as parameter

-- First Stored ProcedureCREATE PROCEDURE SquareSP@MyFirstParam INT
AS
DECLARE @MyFirstParamSquare INT
SELECT @MyFirstParamSquare = @MyFirstParam*@MyFirstParam-- Additional CodeRETURN (@MyFirstParamSquare)GO

get current date in storeprocedure

cast(getdate() as date)
simple and sexy isit?

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Better way for multiple optional search in sql Store Procedure

Better way for multiple optional search in sql Store Procedure

i assigned multiple optional search to my junior resources a few weeks ago recently i gone through the code standards I'm bit surprised every one is different :) . they gone through row-filtering,Dynamic sql,where case in sql,Table search,IF IS NULL in sql ...

Get Store Procedure,User Table,System Table,Primary Key,Scalar Function Create date and modification date in SQL SERVER

Get Store Procedure,User Table,System Table,Primary Key,Scalar Function Create date and modification date in SQL SERVER

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Loop in sql with break or Exit Statement

 Loop in sql with break or Exit Statement

 Loop in sql

Loop with break or Exit Statement

LEN and DATALENGTH in SQL Server

Difference between LEN and DATALENGTH in SQL Server

Len will trim the end of the string and count the length where as  DATALENGTH just count the lenght,its doesn't trim :)

Below sample query and example

select records from particular week days Or datepart in sql

select records from particular week days Or datepart in sql

SELECT AgencyId,CreatedOn FROM Agencies -- List All Records

SELECT AgencyId,CreatedOn FROM Agencies WHERE (datepart (dw, CreatedOn)) IN (7,1) -- List records Only on saturday and sunday

SELECT AgencyId,CreatedOn FROM Agencies WHERE (datepart (dw, CreatedOn)) NOT IN (5,2) -- List all Records EXCEPT Thuesday and Monday
 

If u had any trouble just ask, Happy to help u :) Stay Tune... Have a nice day... 'N happy Coding :)

QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF

When create or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF?

ANSI NULL ON/OFF:
This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

QUOTED IDENTIFIER ON/OFF:
This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.

If u had any trouble just ask, Happy to help u :) Stay Tune... Have a nice day... 'N happy Coding :)

current time in sql and split time from datetime in sql

Hi all,

 Get current time and split time from datetime in sql

DEMO

Remove Duplicate record from Table

Hi *.*,

create table #tbdemo (

id int identity(1,1),

StrName Nvarchar(50),

StrCity Nvarchar(50)

)

insert into #tbdemo values ('Arun','Kollam')

insert into #tbdemo values('Arun','Kollam')

insert into #tbdemo values('Prajeesh','Kochi')

insert into #tbdemo values('Prajeesh','Kochi')

insert into #tbdemo values('Sibi','Patnapuram')

insert into #tbdemo values('Sibi','Patnapuram')

insert into #tbdemo values('Dasapan','Kozikodi')

insert into #tbdemo values('Dasapan','Kozikodi')

select * from #tbdemo

select MIN(id) as ID from #tbdemo

GROUP BY StrName,StrCity

DELETE FROM #tbdemo

WHERE id NOT IN(select MIN(id) as ID from #tbdemo GROUP BY StrName,StrCity)

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Assign Value to a variable on Store Procedure

Hi *.*,
Assign Value to a variable on Store Procedure. First declare a variable with prefix '@'.Then you can use both 
SET and SELECT.