Count number of tables,sps,function or views exist in Database

/* Count Number Of Tables In A Database */
SELECT COUNT(*) AS TABLE_COUNT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

/* Count Number Of Views In A Database */
SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS 

/* Count Number Of Functions In A Database */
SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' 

/* Count Number Of Stored Procedures In A Database */ 
SELECT  COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'

LEFT JOIN with same table

Today i come across a situation where my friend want to show parent column value near to child calumn so he is writing a subquery there. I write the following query

FROM 
TabelA As Parent
Left JOIN
TabelA As Child
On Parent.Id = Child.ParentID

After this query he is getting the perfect result. Avoid the subquery whereever it is possible because it hits the performance badly.

After this query he is writing case statement to append the parent column value in child calumn. I have replace that case statement with followiing

SELECT
Child.Value + ' ' + ISNULL(Parent.Value,'')
FROM 
TabelA As Child
Left JOIN
TabelA As Parent
On Parent.Id = Child.ParentID

Always try to use the SQL in-built function

Add and Drop column from the table

Hi,

Sometimes we have requirement to add or drop a column in a existing Table.

We can drop column using following statement

ADD TABLE table_name

DROP COLUMN column_name;

For adding a column we can use the following statement

ADD TABLE table_name

ADD column_name datatype;

But this column adds in to the table with null value after then we need to update the same column with some default value. We can give the value to the column at the time of adding that using default value constraint with the following statement.

ADD TABLE table_name

ADD column_name datatype DEFAULT(0);

Example :

ADD TABLE Employee

ADD IsActive Bit DEFAULT(0);

Thx,

Rahul

Find the size of parent folder and Child folder using C#

Hi,

Once i went through a requirement in which i need to get the size of each and every folder. I thought if same thing i will do with the code then only folder path i need to pas and i will get the size of each and every child folder.

Below is the C-Sharp code, which gives the size of parent and child folder.

using System;

using System.IO;

class Program

{

    static void Main()

    {

        string path = string.Empty;

        try

        {

            Console.Write("Enter the path of folder : ");

            path = Console.ReadLine();

            GetDirectorySize(path);

        }

        catch (Exception e)

        {

            Console.WriteLine("Exception: " + e.Message);

        }

        finally

        {

            Console.WriteLine("Task done");

        }

        Console.ReadLine();

    }

    static void GetDirectorySize(string p)

    {

        long size = 0;

        Console.Write("Enter the file name of text file");

        Console.Write("(make sure text file should be new, otherwise you may loose your content) :");

        string name = Console.ReadLine();

        StreamWriter sw = new StreamWriter(name + ".txt");

        System.IO.DirectoryInfo dir = new DirectoryInfo(p);

                    FileSystemInfo[] filelist = dir.GetFileSystemInfos();

                    for (int i = 0; i < filelist.Length; i++)

                    {

                        long size1 = 0;

                        if ((filelist[i]).Attributes.ToString().IndexOf("Directory") == 0)

                        {

                            string newPath = (filelist[i]).FullName;

                            System.IO.DirectoryInfo dir1 = new DirectoryInfo(newPath);

                            FileSystemInfo[] filelist1 = dir.GetFileSystemInfos();

                            FileInfo[] fileInfo1;

                            fileInfo1 = dir1.GetFiles("*", SearchOption.AllDirectories);

                            for (int i1 = 0; i1 < fileInfo1.Length; i1++)

                            {

                                try

                                {

                                    size1 += fileInfo1[i1].Length;

                                }

                                catch { }

                            }

                            //Write a line of text

                            sw.WriteLine("Sub Folder : " + newPath + " : Directory size in MB : " + Math.Round((((double)size1) / (1024 * 1024)),2));

                        }

                    }

                    FileInfo[] fileInfo;

                    fileInfo = dir.GetFiles("*", SearchOption.AllDirectories);

                    for (int i = 0; i < fileInfo.Length; i++)

                    {

                        try

                        {

                            size += fileInfo[i].Length;

                        }

                        catch { }

                    }

                    sw.WriteLine("Root Folder : " + p + " : Directory size in MB : " + Math.Round((((double)size) / (1024 * 1024)),2));

                    //Close the file

                    sw.Close();

    }

}

Delete database forcefully

I am not sure how many times you might want to forcefully close all the active connections and drop a database. However, this is a very interesting question.

One option to do this is to take the database in SINGLE USER mode and then issue a DROP DATABASE command.

USE master;
GO
ALTER DATABASE dbname 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE dbname;

How to avoid NOT IN in sql query

Below is the examples to demonstrate the how to avoid NOT IN in SQL query. Not In heats the performance very badly You must have noticed several instances where developers write query as given below.

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO

The query demonstrated above can be easily replaced by Outer JOIN. Indeed, replacing it by Outer JOIN is the best practice. The query that generates the same result as above is shown here using Outer JOIN and WHERE clause in JOIN.
view sourceprint?

/* LEFT JOIN - WHERE NULL */

SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The above example can also be created using Right Outer JOIN.

List of system stored procedure in SQL-Server

Below is the list of some system stored procedure which are helpfull

sp_spaceused [table] - shows you space used by the table
sp_helpindex [table] - shows you index info (same info as sp_help)
sp_helpconstraint [table] - shows you primary/foreign key/defaults and other constraints *
sp_depends [obj] - shows dependencies of an object, for example:
sp_depends [sproc] - shows what tables etc are affected/used by this stored proc
sp_rename [obj] - for renaming database objects (tables, columns, indexes, etc.)
sp_tables - Shows you all the table name in the schema
sp_datatype_info - Shows you all the information for datatype
sp_pkeys [table] - Shows you list of primary key into a table
sp_fkeys [table] - gives the list of foreign key and te tables name in which they are used
sp_databases - gives the list of all the databases 

This query will give us all the stored procedure name in to database
SELECT * FROM sys.procedures;

If you want to see system stored procedure then you can use below query.
SELECT * FROM sys.all_objects WHERE schema_id = 4;