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 :)

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 :)

Creating Database Helper class using Extension method

first of pls go through my previous post abt extension method so that u will get a detail view.

 

static public class DatabaseHelper

    {

        static public string ConnectionString

        {

            get

            {

                return ConfigurationManager.ConnectionStrings["documentDB"].ConnectionString;

            }

        }

        static public DataTable ExecuteQuery(string storedProcedureName, ArrayList parameters)

        {

            SqlConnection connection = new SqlConnection(ConnectionString);

            SqlDataAdapter adapter = new SqlDataAdapter();

            adapter.SelectCommand = new SqlCommand(storedProcedureName, connection);

            foreach (SqlParameter param in parameters)

            {

                adapter.SelectCommand.Parameters.Add(param);

            }

            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet ds = new DataSet();

            adapter.Fill(ds, "tableData");

            return ds.Tables["tableData"];

        }

        static public object ExecuteScaler(string storedProcedureName, ArrayList parameters)

        {

            SqlConnection connection = new SqlConnection(ConnectionString);

            SqlCommand command = new SqlCommand(storedProcedureName, connection);

            command.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter param in parameters)

            {

                command.Parameters.Add(param);

            }

            connection.Open();

            object result=command.ExecuteScalar();

            connection.Close();

            connection.Dispose();

            command.Dispose();

            return result;

        }

        static public SqlParameter CreateIntParameter(string name, int value)

        {

            SqlParameter param = new SqlParameter(name, SqlDbType.Int);

            param.Value = value;

            return param;

        }

        static public SqlParameter CreateStringParameter(string name, string value)

        {

            SqlParameter param = new SqlParameter(name, SqlDbType.NVarChar);

            param.Value = value;

            return param;

        }

        static public SqlParameter CreateDateTimeParameter(string name, DateTime value)

        {

            SqlParameter param = new SqlParameter(name, SqlDbType.DateTime);

            param.Value = value;

            return param;

        }

        static public SqlParameter CreateUniqueIdentifierParameter(string name, Guid value)

        {

            SqlParameter param = new SqlParameter(name, SqlDbType.UniqueIdentifier);

            param.Value = value;

            return param;

        }

    }

 


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

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

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 :)

Security Recommendations For a website

SSL Certificate
When you connect to a secure web server such as https://www.yourwebsite.com, the server authenticates itself to the web browser by presenting a digital certificate. The certificate is proof that an independent trusted third party has verified that the website belongs to the company it claims to belong to. A valid certificate gives customers confidence that they are sending personal information securely, and to the right place.
SSL certificates can provide you with non-forgeable proof of your website's identity, and customer confidence in the integrity and security of your online business. Customers are becoming increasingly aware of the advantages of SSL security and will often not purchase online from non-secure stores. All major web merchants use SSL security to encourage customers to buy online
An SSL certificate contains the following information:
The domain name for which the certificate was issued.
The owner of the certificate and the domain name.
The physical location of the owner.
The validity dates of the certificate.


Coding Recommendations


1.Run application with minimum previleges
To run with the minimum number of privileges needed, follow these guidelines:
Do not run your application with the identity of a system user (administrator).
Run the application in the context of a user with the minimum practical privileges.
Set permissions (ACLs, or Access Control Lists) on all the resources required for your application. Use the most restrictive setting. For example, if practical in your application, set files to be read-only. For a list of the minimum ACL permissions required for the identity of your web application.
Keep files for your Web application in a folder below the application root. Do not allow users the option of specifying a path for any file access in your application. This helps prevent users from getting access to the root of your server.
2.Guard Against Malicious user input
As a general rule, never assume that input you get from users is safe. It is easy for malicious users to send potentially dangerous information from the client to your application. To help guard against malicious input, follow these guidelines:
In forms, filter user input to check for HTML tags, which might contain script.
Never echo (display) unfiltered user input. Before displaying untrusted information, encode HTML to turn potentially harmful script into display strings.
Similarly, never store unfiltered user input in a database.
If you want to accept some HTML from a user, filter it manually. In your filter, explicitly define what you will accept. Do not create a filter that tries to filter out malicious input; it is very difficult to anticipate all possible malicious input.
Do not assume that information you get from the header (usually via the Request object) is safe. Use safeguards for query strings, cookies, and so on. Be aware that information that the browser reports to the server (user agent information) can be spoofed, in case that is important in your application.
If possible, do not store sensitive information in a place that is accessible from the browser, such as hidden fields or cookies. For example, do not store a password in a cookie.
3.Access data securely
Databases typically have their own security. An important aspect Web application security is designing a way for the application to access the database securely.
Use the inherent security of your database to limit who can access database resources. The exact strategy depends on your database and your application:
If practical in your application, use Windows Integrated security so that only Windows-authenticated users can access the database. Integrated security is more secure than using SQL Server standard security.
If your application uses anonymous access, create a single user with very limited permissions, and perform queries by connecting as this user.
Do not create SQL statements by concatenating strings that involve user input. Instead, create a parameterized query and use user input to set parameter values.
If you must store a user name and password somewhere to use as the database login credential, store them securely. If practical, encrypt or hash them.
4.Keep sensitive information safely
If our application transmits sensitive information between the browser and the server, consider using Secure Sockets Layer (SSL).
Use Protected Configuration to secure sensitive information in configuration files such as the Web.config or Machine.config files.
If you must store sensitive information, do not keep it in a Web page, even in a form that you think people will not be able to view (such as in server code).
Use the strong encryption algorithms.
5.Use cookies Securely
Do not store any critical information in cookies. For example, do not store a user's password in a cookie, even temporarily. As a rule, do not store any sensitive information in a cookie that. Instead, keep a reference in the cookie to a location on the server where the information is located.
Set expiration dates on cookies to the shortest practical time you can. Avoid permanent cookies if possible.
Consider encrypting information in cookies.
6.Guard against Denial of service threats
Use error handling (for example, try/catch blocks). Include a finally block in which you release resources in case of failure.
Configure IIS to use throttling, which prevents an application from using a disproportionate amount of CPU.
Test size limits of user input before using or storing it.
Put size safeguards on database queries to help guard against large queries using up system resources.
Put a size limit on file uploads, if those are part of your application.



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

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 :)