Finding Tables having Primary or Foreign Key – SQL Server

Suppose, you want to know if a table has a Primary Key or Foreign Key. We can find out about this in SQL Server using below-mentioned ways.

  • if you want to check if a table has a primary key, you can use below query :

EXEC sp_PKEYS <TableName>

On Transact SQL language the sp_pkeys is system stored procedure which returns primary key information for a single table in the current database.

  • Using Objectproperty function


  • If we want to check if a table has a foreign key then we can use below query :


  • If we want to check for the tables that do not have Primary Key or Foreign Key, then we use below query :


Posted in Technology | Leave a comment

Count all tables, rows, and rows per table in a database

Quite often I receive requests like this:
How big is our database today? How big was it yesterday? Last month? Last year?
In a simple way non-technical people don’t want to know the gigabyte size of the SQL Server master data file or the transaction log file. Those metrics don’t mean anything to them. Instead, they want to know how many tables are in the database, how many rows are in the database (across all tables), and how many rows are in each table.

You can create a simple script for calculating these metrics.

TableName VARCHAR(255) ,
RecordCount INT

EXEC sp_MSforeachtable ‘INSERT #Counts (TableName, RecordCount) SELECT ”?”, COUNT(*) FROM ?’;

FROM #Counts;

SELECT SUM(RecordCount) AS [Rows]
FROM #Counts;

SELECT REPLACE(REPLACE(TableName, ‘[‘, ”), ‘]’, ”) AS TableName ,
RecordCount ,
CAST(CAST(100.0 * RecordCount / ( SELECT SUM(RecordCount) AS [Rows]
FROM #Counts
) AS DECIMAL(5, 2)) AS VARCHAR) + ‘%’ AS Weight
FROM #Counts
ORDER BY TableName ,
RecordCount DESC;

SELECT REPLACE(REPLACE(TableName, ‘[‘, ”), ‘]’, ”) AS EmptyTableName ,
FROM #Counts
WHERE RecordCount = 0
ORDER BY TableName ,
RecordCount DESC;


Here we simply count the records per table and load the results into a temporary table. From this we can get:

a. The number of tables in the database;
b. The number of rows in the database (across all tables);
c. The number of rows in each table; and
d. The empty tables (with no rows).

Above script does not take into consideration the fact that a table might contain a small number of records and still heavily “outweigh” other tables in terms of disk space usage.

For example, a table containing one record that stores a binary copy of a 4GB video is obviously “bigger” (in terms of disk space) than a table containing one hundred records that store mailing addresses in plain text.

When this is an important consideration you can use standard reports in SQL Server like “Disk Usage by Top Tables”, or execute queries like this:

SELECT AS TableName , AS IndexName ,
SUM(p.rows) AS RecordCount ,
SUM(a.total_pages) AS PageCount ,
SUM(a.used_pages) AS UsedPages ,
SUM(a.data_pages) AS DataPages ,
( SUM(a.total_pages) * 8 ) / 1024 AS DiskSpaceMB ,
( SUM(a.used_pages) * 8 ) / 1024 AS UsedSpaceMB ,
( SUM(a.data_pages) * 8 ) / 1024 AS DataSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
AND i.object_id > 255
AND i.index_id <= 1
i.object_id ,
i.index_id ,
ORDER BY OBJECT_NAME(i.object_id);

And here is another useful script, when you need to query for the disk size of your data and log files.

SELECT Type, SUM(size) Size
FROM ( SELECT SUBSTRING(filename, LEN(filename) – 2, 3) Type ,
CAST(size / 128.0 / 1024.0 AS NUMERIC(20,2)) Size
FROM dbo.sysfiles ) T GROUP BY Type
SELECT @@servername AS ServerName ,
DB_NAME() AS DatabaseName ,
FROM ( SELECT Size DataSizeInGB FROM CTE WHERE Type = ‘mdf’ ) [Data]
( SELECT Size LogSizeInGB FROM CTE WHERE Type = ‘ldf’ ) [Log]

Happy Reading !

Posted in Technology | Leave a comment

Team Work

Hello Friends,
Please go through this presentation for understanding the importance of teamwork. You can implement it in your day to day activity and feel the difference.

Enjoy Reading !


Posted in Uncategorized | Leave a comment

Collection in WCF Service

As we know Collection is very much specific to .Net,  WCF does not expose it to the metadata of the service. WCF provides marshaling rules for collection.

Actually, Collections are exposed as array in Service metadata.

Let us understand it with the help of an example.


public interface IStudentService
IEnumerable<StudentInformation> GetStudentInfo(int studentId);

List<StudentInformation> GetStudentData();

public class StudentService : IStudentService
List<StudentInformation> Students = new List<StudentInformation>();

public IEnumerable<StudentInformation> GetStudentInfo(int studentId)
IEnumerable<StudentInformation> Student = from student in Students
where student.StudentId == studentId
select student;

return Student;

public List<StudentInformation> GetStudentData()
List<StudentInformation> Student = (from student in Students
select student).ToList();

return Student;

When we add this service reference at the client side, we can see the collection is exposed as array.

On Client-side, if we try to access returned value of the service method

StudentServiceClient objClient =  new StudentServiceClient();
List<StudentInformation> studentList = objClient.GetStudentData();

we will face following error:

Cannot Implicitly convert type ‘StudentService.StudentInformation[]’ to System.Collections.Generic.List<StudentService.StudentInformation>.

Since, we are returning collection from the service but at the client side we are getting array. There is a disparity at the service end (Collection) and client end (Array). To overcome from this issue, we need to change the setting while adding service reference. While adding service reference, click on “Advance” button. It will open “Service Reference Settings” window. On this window, choose appropriate option for Collection Type i.e. System.Collections.Generic.List option.


Now following code will run smoothly without any issue at the client side,

IEnumerable<StudentInformation> objStudent = objClient.GetStudentInfo(Convert.ToInt16(txtStudentId.Text));

List<StudentInformation> lstStudent = objClient.GetStudentData();

I hope this article will be useful to you. Thanks for reading. Happy Coding .

Posted in Uncategorized | Leave a comment

Serializing and Deserializing Objects…to and from…XML

How do we serialize objects to XML and deserialize the XML back into objects. Let us understand it with the help of an example.

public class MyClass

// school property
private int _Age;

public int Age
get { return _Age; }
set { _Age = value; }

// new school property
public bool Citizen { get; set; }

// there’s nothing wrong with using fields
public string Name;


/// <summary>
/// Serializes an object to an XML string
/// </summary>
/// <param name=”AnyObject”>Object to serialize</param>
/// <returns>XML string</returns>

public static string SerializeAnObject(object AnyObject)
XmlSerializer Xml_Serializer = new XmlSerializer(AnyObject.GetType());
StringWriter Writer = new StringWriter();

Xml_Serializer.Serialize(Writer, AnyObject);
return Writer.ToString();

/// <summary>
/// DeSerialize an object
/// </summary>
/// <param name=”XmlOfAnObject”>The XML string</param>
/// <param name=”ObjectType”>The type of object</param>
/// <returns>A deserialized object…must be cast to correct type</returns>

public static Object DeSerializeAnObject(string XmlOfAnObject, Type ObjectType)
StringReader StrReader = new StringReader(XmlOfAnObject);
XmlSerializer Xml_Serializer = new XmlSerializer(ObjectType);
XmlTextReader XmlReader = new XmlTextReader(StrReader);

Object AnObject = Xml_Serializer.Deserialize(XmlReader);
return AnObject;

Below is the sample code which demonstrate how to use thess functions.

protected void Button1_Click(object sender, EventArgs e)
// create and initialize an object
MyClass Test = new MyClass();

Test.Age = 18;
Test.Name = “Rashid”;
Test.Citizen = true;

//  Serialize it

String XML;
XML = MiscUtilities.SerializeAnObject(Test);

// Deserialize it
MyClass Test2;

Test2 = MiscUtilities.DeSerializeAnObject(XML, typeof(MyClass)) as MyClass;

// TODO:  Get a cup of Tea and bask in the glory of rock solid code.


Happy Learning !

Posted in Uncategorized | Leave a comment

Hello world!

Welcome to This is your first post. Edit or delete it and start blogging!

Posted in Uncategorized | 1 Comment

Birthday Party.

Hi 2 All,
First of all sorry for late in posting new entry in my blog. Yesterday i celebarated my b’lated birthday party with my team mates and other colleagues. Really, it was a great experience for me and a memorable moment in my life. I thanks to all my team mates Specially Archana Mam, Sachin, Anil, Ravi, Sanjay and Janmaya. Also thanks to all my colleagues who came in the party and make it memorable.
Right now there is a song coming in my mind from the movie "Yaadein".
Yaadein yaad aati hai
Yaadein yaad aati hai
Yeh yaadein kisi dil-o-jaanam ke
Chal jaane ke baad aati hain
Yaadein, yaadein, yaadein !


Once again thanks a lot in coming and attending my party .

Posted in Entertainment | Leave a comment

A Very Happy New Year.

 Hi 2 All ,

 May this New Year be filled

 with Sweeteness and Happiness

 in all that you do and may this

 Joy continues the whole year through.

Posted in messages | Leave a comment

Marriage Proposal

Marriage Proposal!



    Baby, I ‘v seen you yesterday while surfing on local train platform and realized that you are the only site I was browsing for. For long    time, I have been lonely; trying to find a bug in my life and you can be a real debugger for me now. My life is just an uncompiled program without you, which never produces an executable code and hence is useless. You are not only beautiful by face but all your ActiveX controls are attractive as well.Your smile is so delightful, which encourages me and gives power to me equal to thousands of mainframes processing power. When you looked at me last evening, I felt like all my program modules were running smoothly and giving expected results. /* which I never experienced before*/.


          With this letter, I just want to convey to you that, if we linked together, I’ll provide you all objects & libraries necessary for a human being to live an error free life. Also don’t bother about the firewall which may be created by   our parents as I’ve strong hacking capabilities by which I’ll ultimately break their security passwords and make them agree for our marriage. I anticipate that nobody is already logged in to your database so that my connect script will fail. And it’s all certain that if this happened to me, I will crash my system beyond recovery.


Kindly interpret this letter properly and grant me all privileges of your inbox.


Only yours,

 XYZ Software Professional

Posted in Entertainment | 1 Comment

Aa bhi jao ….

 Aa bhi jaao …

 Aa bhii jaao kii zindagii kam hai

 Tum nahiin ho to har Khushii kam hai

 Vaadaa kar ke ye kaun aayaa nahiin

 Shahar mein aaj raushanii kam hai

 Jaane kyaa ho gayaa hai mausam ko

 Dhuup ziyaadaa hai chaandnii kam hai

 Aaiinaa dekh kar Khayaal aayaa

 Aaj kal un kii dostii kam hai

 Tere dam se hii main mukammal huun

 Bin tere terii ‘Dosti’ kam hai . 

Posted in Entertainment | Leave a comment