T-SQL to find all tables without primary key in SQL Server

As per the best practice, it is always advisable to have a Primary key on all tables in SQL Server Database. Now many might not know this and have created tables without any primary key. As a Good DBA your job is to find out all the table which are not having the primary key and then based on recommendation you can create a Primary key. Below piece of code will assist you in getting all the tables which are not having any primary key defined.

How to restore database from SQL Server Split Backup

In my Previous post Split Backup in SQL Server I had written on how to split your sql server backup into multiple files. Now that you have taken the backup by splitting file, How will you restore it.

Restoring a database from SQL Server Split backup is pretty simple. Below is the T-SQL script to restore database from SQL Server Split Backup

Split Backup in SQL Server

How many times we come across a situation where the backup size of a SQL Server database is too huge and you run out of space on a single disk. You are having enough space but they are splitted on different drives. 

Microsoft has provided a beautiful solution for this. Its Split backup in SQL Server. Some of the advantages of Split backups are

1. You have split the backup file into different smaller pieces and then place different piece in different drive. This reduces your issues of having large space in single drive.

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

You might come across this error while running SSIS Package.

There can be two scenarios where you might face this error.

1. When you are running through, BIDS (SQL Server Business Intelligence Development Studio)

2. You are running SSIS Package through a SQL Server Agent Job.

To resolve this issue,

When you are running through, BIDS (SQL Server Business Intelligence Development Studio),

1. Go to Project Menu --> Select YourProjectProperties

How to export data from sql server to excel using import export wizard

How many times we have the need to export our data in SQL Server Database to Excel for some manipulations or any other stuff. I guess many times? So how do we achieve this?

There are two ways of achieving this. One is using the inbuilt option of Import Export feature in SQL Server using SQL Server Management Studio and other way around is using SQL Server Integration Services (SSIS) which is a bit robust and customisable compared to Native Import Export Wizard.

Useful Dynamic Management Views in SQL Server 2008

Here is a list of some DMVs that are important in troubleshooting server performance and can be useful in identifying poorly performing queries:

 

sys.dm_exec_requests and sys.dm_exec_sessions
These DMVs can be used to view active sessions and requests running on a server, to identify long running sessions.

Example:
How to find information about blocked requests:

Column Table Information In Sql Server

There are numerous occasions on daily bases where we need some basic information of the Tables and Columns used in a Database for many different purpose.

Here is a small script which gives you some basic information of Tables and Columns. There are many more columns in the catalogs which I have used, which you can modify, add, Remove, Customise as per your needs.

Pages