In this article, I will tell you a SQL Script to Create Country table and populate with all countries. In this case, we either use a text box where user enters the country name or provide user with a drop down list of all countries. To create the dropdown, it is advised to store country names in a database table. Write the following code:

CREATE TABLE [dbo].[Country]( 
[ID] [int] IDENTITY(1,1) NOT NULL, 
[CountryName] [nvarchar](100) NOT NULL 
) ON [PRIMARY] 
 
GO 
SET IDENTITY_INSERT [dbo].[Country] ON 
 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Afghanistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Albania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Algeria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'American Samoa') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Andorra') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Angola') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Anguilla') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Antarctica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Antigua And Barbuda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Argentina') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Armenia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Aruba') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Australia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Austria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Azerbaijan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bahamas') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bahrain') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bangladesh') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Barbados') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Belarus') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Belgium') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Belize') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Benin') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bermuda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bhutan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bolivia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bosnia And Herzegowina') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Botswana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bouvet Island') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Brazil') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'British Indian Ocean Territory') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Brunei Darussalam') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bulgaria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Burkina Faso') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Burundi') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cambodia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cameroon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Canada') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cape Verde') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cayman Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Central African Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Chad') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Chile') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'China') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Christmas Island') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cocos (Keeling) Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Colombia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Comoros') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Congo') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cook Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Costa Rica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cote D''Ivoire') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Croatia (Local Name: Hrvatska)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cuba') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cyprus') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Czech Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Denmark') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Djibouti') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Dominica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Dominican Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'East Timor') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ecuador') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Egypt') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'El Salvador') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Equatorial Guinea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Eritrea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Estonia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ethiopia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Falkland Islands (Malvinas)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Faroe Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Fiji') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Finland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'France') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'French Guiana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'French Polynesia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'French Southern Territories') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Gabon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Gambia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Georgia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Germany') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ghana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Gibraltar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Greece') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Greenland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Grenada') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guadeloupe') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guam') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guatemala') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guinea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guinea-Bissau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guyana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Haiti') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Heard And Mc Donald Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Holy See (Vatican City State)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Honduras') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Hong Kong') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Hungary') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Iceland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'India') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Indonesia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Iran (Islamic Republic Of)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Iraq') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ireland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Israel') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Italy') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Jamaica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Japan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Jordan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kazakhstan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kenya') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kiribati') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Korea, Dem People''S Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Korea, Republic Of') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kuwait') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kyrgyzstan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lao People''S Dem Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Latvia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lebanon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lesotho') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Liberia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Libyan Arab Jamahiriya') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Liechtenstein') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lithuania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Luxembourg') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Macau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Macedonia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Madagascar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Malawi') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Malaysia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Maldives') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mali') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Malta') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Marshall Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Martinique') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mauritania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mauritius') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mayotte') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mexico') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Micronesia, Federated States') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Moldova, Republic Of') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Monaco') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mongolia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Montserrat') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Morocco') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mozambique') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Myanmar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Namibia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nauru') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nepal') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Netherlands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Netherlands Ant Illes') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'New Caledonia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'New Zealand') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nicaragua') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Niger') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nigeria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Niue') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Norfolk Island') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Northern Mariana Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Norway') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Oman') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Pakistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Palau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Panama') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Papua New Guinea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Paraguay') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Peru') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Philippines') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Pitcairn') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Poland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Portugal') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Puerto Rico') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Qatar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Reunion') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Romania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Russian Federation') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Rwanda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saint K Itts And Nevis') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saint Lucia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saint Vincent, The Grenadines') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Samoa') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'San Marino') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sao Tome And Principe') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saudi Arabia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Senegal') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Seychelles') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sierra Leone') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Singapore') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Slovakia (Slovak Republic)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Slovenia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Solomon Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Somalia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'South Africa') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'South Georgia , S Sandwich Is.') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Spain') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sri Lanka') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'St. Helena') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'St. Pierre And Miquelon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sudan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Suriname') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Svalbard, Jan Mayen Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sw Aziland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sweden') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Switzerland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Syrian Arab Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Taiwan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tajikistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tanzania, United Republic Of') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Thailand') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Togo') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tokelau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tonga') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Trinidad And Tobago') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tunisia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Turkey') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Turkmenistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Turks And Caicos Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tuvalu') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Uganda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ukraine') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United Arab Emirates') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United Kingdom') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United States') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United States Minor Is.') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Uruguay') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Uzbekistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Vanuatu') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Venezuela') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Viet Nam') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Virgin Islands (British)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Virgin Islands (U.S.)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Wallis And Futuna Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Western Sahara') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Yemen') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Yugoslavia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Zaire') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Zambia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Zimbabwe') 
GO 
SET IDENTITY_INSERT [dbo].[Country] OFF 
GO 

Recently one of our costumer emailed us below question:

Hi Hostforlife,
Need your urgent help. In recent past, we have been attacked by the hacker who was able to get in to our SQL Server via sysadmin account and made big damage to our data. To make sure it doesn’t happen in future, I have taken task to find out SQL Server password which are weak.
Do you have any suggestions for me?

This is one of the area which is always haunting all SQL DBAs. There are recommendations to use Windows Authentication to connect to SQL Server and that would save from all such problem. But it is not always feasible to use Windows Authentication. Now, if you decided to choose SQL Authentication, there is a setting which is “Enforce Password Policy” which would ensure that you are choosing a strong password.

If recommendations are not followed, you might end up in situation where SQL Logins have weak and basic passwords. SQL Server has provided a function PWDCOMPARE which can become very useful to find known password. Below are few example use of this out of box funtion:

SELECT NAME,
NAME 'password'
FROM   sys.sql_logins
WHERE  Pwdcompare(NAME, password_hash) = 1
UNION
SELECT NAME,
'<blank>' AS 'password'
FROM   sys.sql_logins
WHERE  Pwdcompare('', password_hash) = 1
UNION
SELECT NAME,
'password123' AS 'password'
FROM   sys.sql_logins
WHERE  Pwdcompare('password123', password_hash) = 1

In above query, we are trying to find:

•     Password same as user name – first query
•     Blank password – second query
•     Password = password123 – third query

These are one of the most common password used in the industry. I am sure you can extend this further by modifying it and adding more weak passwords.

Here is the sample output for the above:

Hope this article would help you in finding weak passwords and make it more complex.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

In this post, I will tell you about how to return more than one table from store procedure in SQL Server 2014. Return a single table full of data from store procedure we are able to use a DataTable however to come back multiple tables from store procedure we've got to use DataSet. DataSet could be a bunch of DataTables. so the following code you'll use to return single table. SqlCommand

cmd = new SqlCommand("sp_Login", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
   con.Open();
}
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
return dt;

Now write the store procedure like the following code:
CREATE PROCEDURE sp_Test
    @Email nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;
    select * from <tblName> whhere email = @Email
END

And then, It’s time to found how to get multiple table value in one DataSet. Write the code below:
CREATE PROCEDURE sp_Test
AS
BEGIN   
    SET NOCOUNT ON;
    select * from Tbl1
    select * from Tbl2
    select * from Tbl3
    select * from Tbl4
END

And here is the C# code:
SqlCommand cmd = new SqlCommand("sp_test", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{   
con.Open();
}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
con.Close();
// Retrieving total stored tables from DataSet.             
DataTable dt1 = ds.Tables[0];
DataTable dt1 = ds.Tables[1];
DataTable dt1 = ds.Tables[2];
DataTable dt1 = ds.Tables[3];

I hope it works for you!

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

European Windows and ASP.NET Spotlight Hosting Partner in Europe, HostForLIFE.eu, has announced the availability of new hosting plans that are optimized for the latest update of the Drupal 7.37 hosting technology.

HostForLIFE.eu hosts its servers in top class data centers that is located in Amsterdam (NL), London (UK), Paris (FR), Frankfurt(DE) and Seattle (US) to guarantee 99.9% network uptime. All data center feature redundancies in network connectivity, power, HVAC, security, and fire suppression. All hosting plans from HostForLIFE.eu include 24×7 support and 30 days money back guarantee. The customers can start hosting our Drupal site on our environment from as just low €3.00/month only.

Drupal is an open source content management platform powering millions of websites and applications. Thousands of add-on modules and designs let you build any site you can imagine. Drupal 7.37 Includes bug fixes and small API/feature improvements only (no major new functionality); major, non-backwards-compatible new features are only being added to the forthcoming Drupal 8.0 release. If you are looking for the right Windows ASP.NET Hosting provider that support Drupal 7.37, we are the right choice for you.

The 7.37 update also includes fixed a regression in Drupal 7.36 which caused certain kinds of content types to become disabled if we were defined by a no-longer-enabled module, removed a confusing description regarding automatic time zone detection from the user account form (minor UI and data structure change), allowed custom HTML tags with a dash in the name to pass through filter_xss() when specified in the list of allowed tags, allowed hook_field_schema() implementations to specify indexes for fields based on a fixed-length column prefix (rather than the entire column), as was already allowed in hook_schema() implementations, fixed PDO exceptions on PostgreSQL when accessing invalid entity URLs, added a sites/all/libraries folder to the codebase, with instructions for using it and added a description to the "Administer text formats and filters" permission on the Permissions page (string change).

HostForLIFE have hosted large numbers of websites and blogs until now. Our clients come from diverse backgrounds from all sectors of the economy. HostForLIFE.eu clients are specialized in providing supports for Drupal for many years. We are glad to provide support for European Drupal 7.37 hosting users with advices and troubleshooting for our client website when necessary.

HostForLIFE.eu is a popular online Windows based hosting service provider catering to those people who face such issues. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market. Our powerful servers are especially optimized and ensure Drupal 7.37 performance. We have best data centers on three continent, unique account isolation for security, and 24/7 proactive uptime monitoring.

For more information about this new product, please visit http://hostforlife.eu/European-Drupal-737-Hosting

About HostForLIFE.eu
HostForLIFE.eu is an European Windows Hosting Provider which focuses on the Windows Platform only. HostForLIFE.eu deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.asp.net/hosting/hostingprovider/details/953). Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, we have also won several awards from reputable organizations in the hosting industry and the detail can be found on our official website.

In this tutorial, I will show you how to use extended events to identify page splits in SQL Server. The easiest way to track if page splits are happening in SQL Server is to use the PerfMon Counters. For the records you can start from  “SQL Server: Access Methods -> Page Splits/sec”. This counter is quite handy to understand if this behavior happens in our SQL Server instance.

Step 1

Let us first create a database for our experiment. We will also create our Extended Event to track the PageSplits.

-- Create a dummy database
CREATE DATABASE PageSplits
GO
-- Get the DB ID, we will use it in next step
SELECT DB_ID('PageSplits')
GO
-- Create Session
CREATE EVENT SESSION [Catch_PageSplit] ON SERVER
ADD EVENT sqlserver.page_split(
WHERE ([package0].[equal_uint64]([database_id],(10))))  -- NOTE: Please make sure you change the DATABASE_ID
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF)
GO
-- Start the event session
ALTER EVENT SESSION [Catch_PageSplit] ON SERVER
STATE = start;
GO

Step 2

Before we get to the next step, let us start monitoring our Extended Events for any possible page splits.

USE PageSplits
GO
CREATE TABLE mytest (
myCompany CHAR(5) NOT NULL CONSTRAINT pk_mytest PRIMARY KEY CLUSTERED,
FillData VARCHAR(3000) NOT NULL
)
GO
INSERT mytest ( myCompany, FillData )
VALUES( '00001', REPLICATE( 'A', 3000 ) ),
( '00002', REPLICATE( 'B', 1000 ) ),
( '00003', REPLICATE( 'C', 3000 ) ),
( '00004', REPLICATE( 'A', 3000 ) ),
( '00005', REPLICATE( 'B', 1000 ) ),
( '00006', REPLICATE( 'C', 3000 ) ),
( '00007', REPLICATE( 'A', 3000 ) ),
( '00008', REPLICATE( 'B', 1000 ) ),
( '00009', REPLICATE( 'C', 3000 ) )
GO

Step 3

Next step is to create a table with some values. Later we will use this to create a page split scenario:

Step 4

Then, create the scenario of page split by updating a row with some extra data.

-- Update to introduce a page split
UPDATE mytest
SET FillData = REPLICATE( 'B', 3000)
WHERE myCompany = '00002'
GO

Don’t forget to look at the Live Data feed for entries. If the Page Split happen you will see something like:

As you can see, this Page Split has happened because of an Update. The two pages under question are also shown. We can also see the database ID under question that caused the page split.

Step 5

Now that we learnt something new here, let us clean up the database we just created using this code:
-- Clean up time
USE MASTER
GO
DROP DATABASE PageSplits
GO
DROP EVENT SESSION [Catch_PageSplit] ON SERVER
GO

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

Here during this example I'll show you the way to execute a task or call a store procedure at a specific time(like at midnight) using SQL Server or I will say the way to schedule a job at a specific time. Sometimes you have got to perform something daily, weekly or at a selected gap of your time. Repeat a similar task when by a person is little bit difficult. So, in this case SQL is here to solve your problem using SQL Job scheduler. This is often a task you fixed in your server, set timer on at particular time and write down the SQL query to perform. That is all you have got to do. Let’s see the way to schedule your Job at a selected time.

Open your Management Studio and check the SQL Server Agent. Begin the SQL Server Agent by following steps.

Next step, go to Job section in your SQL Server Agent and select New Job, shown on the below picture:

After choosing the New Job, a new window will be opened with all the properties of a New Job. Now it’s time to fill up each and every field according to your specification. In the General tabs write down any name according to your project and left the others as it is.

Now, in the “Steps”, click on the New button, and a new window will be opened as shown on the following picture:

Write a name for your step and your query to be executed and select on the OK button.

Now, in the Schedule tab follow the steps as the following image:

The only thing is left is to start your job. To start the job follow this steps.

After successfully starting of the job you will get a successful alert message shown on the following picture:

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

Here I am going to share how can you take the database backup of all the SQL Server databases at once instead of taking it individually.

First, you need to create a folder in any drive to store the backup of database which you are going to take. For example, you have to create a folder named "dbBackup" in my drive "C:\"

Then, copy the below code & paste it in your SQL Server Query Executer page:

    DECLARE @DBName varchar(255)
    DECLARE @DATABASES_Fetch int
    DECLARE DATABASES_CURSOR CURSOR FOR
        select
            DATABASE_NAME   = db_name(s_mf.database_id)
        from
            sys.master_files s_mf
        where
           -- ONLINE
            s_mf.state = 0

           -- Only look at databases to which we have access
        and has_dbaccess(db_name(s_mf.database_id)) = 1

            -- Not master, tempdb or model
        and db_name(s_mf.database_id) not in ('Master','tempdb','model')
        group by s_mf.database_id
        order by 1

    OPEN DATABASES_CURSOR

    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        declare @DBFileName varchar(256)   
        set @DBFileName = datename(dw, getdate()) + ' - ' +
                           replace(replace(@DBName,':','_'),'\','_')

        exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''c:\dbBackup\' +
            @DBFileName + '.bak' + ''' WITH NOFORMAT, INIT,  NAME = N''' +
            @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100')

        FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
    END

    CLOSE DATABASES_CURSOR
    DEALLOCATE DATABASES_CURSOR

Now execute the page and check your Folder "dbBackup" in Drive "C:/" . You will find all the databases backup in it.

Now, you are done! Simple, right?

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

In this tutorial, I'm gonna tell you about inserting large amount of random data into sql server tables for performance testing. 

Just follow these step to create a large table with random data for performance testing:

Step 1

If Table exists drop the tables:

If (Exists (select *
            from information_schema.tables
            where table_name = 'tblProductSales'))
Begin
 Drop Table tblProductSales
End

If (Exists (select *
            from information_schema.tables
            where table_name = 'tblProducts'))
Begin
 Drop Table tblProducts
End

Step 2

Then you can recreate the tables:

Create Table tblProducts
(
 [Id] int identity primary key,
 [Name] nvarchar(50),
 [Description] nvarchar(250)
)

Create Table tblProductSales
(
 Id int primary key identity,
 ProductId int foreign key references tblProducts(Id),
 UnitPrice int,
 QuantitySold int
)

Step 3

Insert Sample data into tblProducts table

Declare @Id int
Set @Id = 1

While(@Id <= 300000)
Begin
 Insert into tblProducts values('Product - ' + CAST(@Id as nvarchar(20)),
 'Product - ' + CAST(@Id as nvarchar(20)) + ' Description')
 
 Print @Id
 Set @Id = @Id + 1
End

Step 4

Declare variables to hold a random ProductId, UnitPrice and QuantitySold

declare @RandomProductId int
declare @RandomUnitPrice int
declare @RandomQuantitySold int

Declare and set variables to generate a random ProductId between 1 and 100000

declare @UpperLimitForProductId int
declare @LowerLimitForProductId int

set @LowerLimitForProductId = 1
set @UpperLimitForProductId = 100000

Declare and set variables to generate a random UnitPrice between 1 and 100

declare @UpperLimitForUnitPrice int
declare @LowerLimitForUnitPrice int

set @LowerLimitForUnitPrice = 1
set @UpperLimitForUnitPrice = 100

Declare and set variables to generate a random QuantitySold between 1 and 10

declare @UpperLimitForQuantitySold int
declare @LowerLimitForQuantitySold int

set @LowerLimitForQuantitySold = 1
set @UpperLimitForQuantitySold = 10

Step 5

Now, you have to insert Sample data into tblProductSales table

Declare @Counter int
Set @Counter = 1

While(@Counter <= 450000)
Begin
 select @RandomProductId = Round(((@UpperLimitForProductId - @LowerLimitForProductId) * Rand() + @LowerLimitForProductId), 0)
 select @RandomUnitPrice = Round(((@UpperLimitForUnitPrice - @LowerLimitForUnitPrice) * Rand() + @LowerLimitForUnitPrice), 0)
 select @RandomQuantitySold = Round(((@UpperLimitForQuantitySold - @LowerLimitForQuantitySold) * Rand() + @LowerLimitForQuantitySold), 0)
 
 Insert into tblProductsales
 values(@RandomProductId, @RandomUnitPrice, @RandomQuantitySold)

 Print @Counter
 Set @Counter = @Counter + 1
End

Step 6

Finally, check the data in the tables using a simple SELECT query to make sure the data has been inserted as expected.
Select * from tblProducts
Select * from tblProductSales

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

In this post, let me explain about How to List Out SQL Logins and Database User Mappings.

You can use system stored procedure sp_msloginmappings to list out the SQL logins and database user mappings. And here is the syntax:
sp_msloginmappings @Loginname , @Flags

@Loginname – Optional argument, in case if you not specify the Login name procedure will return the result for all the SQL Server logins
@Flags – You can specify value 0 or 1, 0 value will show user mapping to all databases and 1 will show the user mapping to current database only. Default value is 0
use master
go
exec sp_msloginmappings 'sa', 0

use master
go
exec sp_msloginmappings 'sa', 1

If you want to run the sp_msloginmappings across multiple SQL Instance using either Central management server or Powershell, write the following script:
create table #loginmappings( 
 LoginName  nvarchar(128) NULL, 
 DBName     nvarchar(128) NULL, 
 UserName   nvarchar(128) NULL, 
 AliasName  nvarchar(128) NULL
)   
insert into #loginmappings
EXEC master..sp_msloginmappings
select * from #loginmappings
 drop table #loginmappings

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

Indexes play an important role in SQL Server performance. Coins always have two sides, just like a well-designed index can improve query performance and an incorrect index can impact query performance. So it is important to find which indexes are not being used. It helps us to reduce storage and reduce the overhead of the database engine to maintain unused indexes. But how can we find indexes that are not being used? In this article, I'm gonna tell you how to identify unused indexes in SQL Server.

The absence of an index can result in table or index scans that reduce performance in some case and also too many indexes require extra storage and extra effort to maintain the database and it might slow down insert / update operations. One of the approaches to improve the overall performance is keep used indexes but drop all unused indexes.

"dm_db_index_physical_stats" is a dynamic management view related to index statistics. This view gives information about indexes used or unused, it complete or missing some columns is irrelevant. This dynamic view has many important columns like user_seeks (number of seeks by user queries), user_scans (number of scans by user queries), user_lookups (number of bookmark lookups by user queries) and a combination of these three columns provide us a total read count. The column user updates (number of updates by user queries) indicates the level of maintenance on the index caused by insert / update / delete operations on the table or view. A proper join among these DVM and system tables such as indexes, objects and schemas enable us to list all unused indexes for a single database.

The following query helps us to find unused indexes in our database:

    SELECT  
    o.name AS TableName, 
    i.name AS Indexname, 
    i.is_primary_key AS PrimaryKey, 
    s.user_seeks + s.user_scans + s.user_lookups AS NumOfReads, 
    s.user_updates AS NumOfWrites, 
    (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) AS TableRows, 
    'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) AS 'DropStatement' 
    FROM sys.dm_db_index_usage_stats s  
    INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id  
    INNER JOIN sys.objects o ON s.object_id = o.object_id 
    INNER JOIN sys.schemAS c ON o.schema_id = c.schema_id 
    WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 
    AND s.databASe_id = DB_ID()  
    AND i.type_desc = 'NONCLUSTERED' 
    AND i.is_primary_key = 0 
    AND i.is_unique_constraint = 0 

The preceding query includes the following helpful information.

•     Table name
•     Index name
•     Primary key
•     Number of read count
•     Number of writes
•     Total number of rows
•     Drop statement

The following  is the output of the preceding query:

After running the preceding query for the database it will list all the non-clustered indexes for all tables. Now we can determine the unused indexes by comparing the number of reads applied to an index with the number of writes. If we have a number of reads (NumOfReads column in the preceding query) then the indexes are not being used.

Base on the query result and application knowledge, we may decide which index needs to be dropped and the last column of the query contains a drop index statement.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.