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.
Recent blog posts
- MetaDapper: Data Mapping and Conversion Made Easy With the Right Tools
- How to Tune Microsoft SQL Server for Performance
- sql database tuning for developers
- Violation of PRIMARY KEY constraint ''. Cannot insert duplicate key in object ''. The duplicate key value is ()
- Cannot enable change tracking on table ''. Change tracking requires a primary key on the table.
- Error: The backup data at the end of "" is incorrectly formatted
- T-SQL To Get Count Of Records In All Tables In All Databases In SQL Server
- How To Create SQL Server Audit Feature
- How To Get the Publication Name of a Replicated Table
- How To Change SQL Server Service Account Password
You want to know how many Primary Keys are existing on a SQL Server database? Below script can assist you in all the details of all Primary Keys in a Database. Simply run the below script against the database for which you want to get the Primary Key details.
Below is a small yet powerful script to get all the details you want to know about the Indexes in your database.
Simply run the below Code against the database for which you want to get the Index details.
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
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.
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
- Open SQL Server Business Intelligence Development Studio (BIDS). Click on File a New a Project.
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.
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.
How to find information about blocked requests:
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.