SQL Server: List all the databases

Lists databases that either reside in an instance of the SQL Server 2005 Database Engine or are accessible through a database gateway.



EXEC sp_databases

or,

SELECT name

FROM sys.databases

Resetting the id of a table having an identity column

I need to truncate the table having foreign keys. 
As this is not allowed, I deleted all the rows from the table and re-indexed the identity column of the table as this:


dbcc checkident('employee',reseed, 0)


where, employee is the name of table,
            reseed specifies that the current identity value should be changed,
and,    0 is the new seed value to use in reseeding the identity column


I did this in MS SQL Server 2005

Visual Studio.NET IDE: Clear Recent Projects List

Simply navigate to the following location in registry editor.

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList


Now, delete the entries

SQL Server : Truncate Database

I needed to clear or delete data in all tables of a database. I googled it and found out this:


CREATE PROCEDURE sp_EmplyAllTable
AS
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Attaching SQL Server database using C#

private void AttachDatabase(string databaseName, string fileName, string loginName, string password)
{
     SqlConnection conn = new SqlConnection();
     SqlCommand cmd = new SqlCommand("", conn);


     conn.ConnectionString = "Data Source=" + serverName + ";Initial Catalog=Master;User Id = " + loginName + ";Password = " + password + "";


     cmd.CommandText = "exec sp_attach_single_file_db @dbname='" + databaseName + "',@physname='" + fileName + "'";


     try
     {
          conn.Open();
          cmd.ExecuteNonQuery();


          MessageBox.Show("Database Attached Successfully", "Attach Database");
     }
     catch (Exception)
     {
          //throw ex;
          MessageBox.Show("Could Not Attach Database \nLogin Failed for user " + loginName, "Attach Database");
     }
     finally
     {
          cmd.Dispose();
          conn.Dispose();
     }
}

C#: List all SQL Server Instances on a local network

I needed the list of SQL Server Instances to setup the database for my application to the particular server.


I did this with the help of google and stackoverflow. The code is as following:



private void ListSqlServers()
{
     string myServer = Environment.MachineName;
     DataTable servers = SqlDataSourceEnumerator.Instance.GetDataSources();


     for (int i = 0; i < servers.Rows.Count; i++)
     {
          // Remove the following 'if' condition 
          // to list the servers of local machine
          // along with network servers


          // used to get the servers in the local machine
          if (myServer == servers.Rows[i]["ServerName"].ToString()) 
          {
               if ((servers.Rows[i]["InstanceName"] as string) != null)
                    comboBox1.Items.Add(servers.Rows[i]["ServerName"] + "\\" + servers.Rows[i]["InstanceName"]);
               else
                    comboBox1.Items.Add(servers.Rows[i]["ServerName"]);
          }
     }
}