SELECT name INTO #tables from sys.objects where type = 'U'

while (SELECT count(1) FROM #tables) > 0

begin

declare @sql varchar(max)

declare @tbl varchar(255)

SELECT top 1 @tbl = name FROM #tables

SET @sql = 'drop table ' + @tbl

exec(@sql)

DELETE FROM #tables where name = @tbl

end

DROP TABLE #tables

Got these very useful references from http://sqltutorials.blogspot.com/2007/06/sql-string-functions.html

SQL String Functions

Sql string function is a built-in string function.
It perform an operation on a string input value and return a string or numeric value.
Below is All built-in Sql string function :
ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE, UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM
Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)
SELECT ASCII(’a') — Value = 97
SELECT ASCII(’b') — Value = 98
SELECT ASCII(’c') — Value = 99
SELECT ASCII(’A') — Value = 65
SELECT ASCII(’B') — Value = 66
SELECT ASCII(’C') — Value = 67
SELECT ASCII(’1′) — Value = 49
SELECT ASCII(’2′) — Value = 50
SELECT ASCII(’3′) — Value = 51
SELECT ASCII(’4′) — Value = 52
SELECT ASCII(’5′) — Value = 53
Example SQL String Function - SPACE
-Returns spaces in your SQL query (you can specific the size of space).
Syntax - SPACE ( integer)
SELECT (’SQL’) + SPACE(0) + (’TUTORIALS’)
– Value = SQLTUTORIALS
SELECT (’SQL’) + SPACE(1) + (’TUTORIALS’)
– Value = SQL TUTORIALS
Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX(’SQL’, ‘Well organized understand SQL tutorial’)
– Value = 27
SELECT CHARINDEX(’SQL’, ‘Well organized understand SQL tutorial’, 20)
– Value = 27
SELECT CHARINDEX(’SQL’, ‘Well organized understand SQL tutorial’, 30)
– Value = 0 (Because the index is count from 30 and above)
Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( ’string1′ , ’string2′ , ’string3′ )
SELECT REPLACE(’All Function’ , ‘All’, ‘SQL’)
– Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( ’string’ [ , 'quote_character' ] )
SELECT QUOTENAME(’Sql[]String’)
– Value = [Sql[]]String]

Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 )
SELECT STUFF(’SqlTutorial’, 4, 6, ‘Function’)
– Value = SqlFunctional
SELECT STUFF(’GoodMorning’, 5, 3, ‘good’)
– Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer)
SELECT LEFT(’TravelYourself’, 6)
– Value = Travel
SELECT LEFT(’BeautyCentury’,6)
– Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT(’TravelYourself’, 6)
– Value = urself
SELECT RIGHT(’BeautyCentury’,6)
– Value = Century

Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)
SELECT REPLICATE(’Sql’, 2)
– Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING(’SQLServer’, 4, 3)
– Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string)
SELECT LEN(’SQLServer’)
– Value = 9


Example SQL String Function - REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)
SELECT REVERSE(’SQLServer’)
– Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char)
SELECT UNICODE(’SqlServer’)
– Value = 83 (it take first character)
SELECT UNICODE(’S')
– Value = 83

Example SQL String Function - LOWER
-Convert string to lowercase.
Syntax - LOWER( string )
SELECT LOWER(’SQLServer’)
– Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string )
SELECT UPPER(’sqlserver’)
– Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )
SELECT LTRIM(’ sqlserver’)
– Value = ’sqlserver’ (Remove left side space or blanks)
Example SQL String Function - RTRIM
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )
SELECT RTRIM(’SqlServer ‘)
– Value = ‘SqlServer’ (Remove right side space or blanks)

The Stored Procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
-- =============================================
-- Author:        <Marcus Le Count>
-- Create date: <22 November 2008>
-- Description:    <Inserts a new record into the Accounts Table>
-- =============================================

ALTER PROCEDURE [dbo].[CreateNewAccount]
    (
    @AccountName nvarchar(50),
    @OpeningBalance money,
    @Balance money,
    @DateCreated datetime,
    @LastUpdate datetime,
    @AccountType int,
    @CreatedBy int
    )
AS
DECLARE @MyIdentity int
BEGIN
    INSERT INTO [accounts]
           ([accountName]
           ,[balance]
           ,[openingBalance]
           ,[dateCreated]
           ,[dateLastUpdate]
           ,[accountType]
           ,[createdBy])
     VALUES
           (@AccountName
            ,@Balance
            ,@OpeningBalance
            ,@DateCreated
            ,@LastUpdate
            ,@AccountType
            ,@CreatedBy)

    SELECT @MyIdentity = SCOPE_IDENTITY()

    SELECT @MyIdentity AS [MyIdentity]

RETURN

END

 

The ASP.NET code

public String CreateNewAccount()
    {
        SqlConnection conn;
        SqlCommand comm;

        string newID;

        //read the connection string from web.config
        string connectionString = ConfigurationManager.ConnectionStrings[
        "DefaultDatabase"].ConnectionString;

        //Initialize connection
        conn = new SqlConnection(connectionString);

        //create command (using stored Procedure)
        comm = new SqlCommand("CreateNewAccount", conn);
        comm.CommandType = CommandType.StoredProcedure;

        //add the command parameters
        comm.Parameters.AddWithValue("@AccountName", System.Data.SqlDbType.NVarChar);
        comm.Parameters["@AccountName"].Value = m_AccountName;
        comm.Parameters.AddWithValue("@OpeningBalance", System.Data.SqlDbType.Money);
        comm.Parameters["@OpeningBalance"].Value = m_OpeningBalance;
        comm.Parameters.AddWithValue("@Balance", System.Data.SqlDbType.Money);
        comm.Parameters["@Balance"].Value = m_Balance;
        comm.Parameters.AddWithValue("@DateCreated", System.Data.SqlDbType.DateTime);
        comm.Parameters["@DateCreated"].Value = m_DateCreated;
        comm.Parameters.AddWithValue("@LastUpdate", System.Data.SqlDbType.DateTime);
        comm.Parameters["@LastUpdate"].Value = m_LastUpdate;
        comm.Parameters.AddWithValue("@AccountType", System.Data.SqlDbType.Int);
        comm.Parameters["@AccountType"].Value = m_AccountType;
        comm.Parameters.AddWithValue("@CreatedBy", System.Data.SqlDbType.Int);
        comm.Parameters["@CreatedBy"].Value = m_CreatedBy;
        try
        {
            //open the connection
            conn.Open();

            //execute the command
            newID = Convert.ToString(comm.ExecuteScalar());
            return newID;
        }
        catch
        {
            return "false";
        }
        finally
        {
            //close the connection
            conn.Close();
            comm.Dispose();
        }

    }

To get the current date in a SQL query use the GETDATE() function.

SELECT * FROM homeopens
WHERE  opendate > GETDATE()

A FULL OUTER JOIN simply returns ALL records on both sides. The example below would return all Customers and all Transactions regardless of any match between them.

SELECT Customers.Name, Transaction.Amount, Transaction.Date
FROM Customers FULL OUTER JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

A RIGHT OUTER JOIN retrieves data that is common in both tables AND any records from table two (the one on the right hand side of the join) that doesn’t have a corresponding record in the left table.

The example below will return results for Transactions even if no Customer record is associated to the Transaction. (Not a great example I know, cos when would you ever have a transaction with out a customer? Unlikely I know, but you see the principle, right)

SELECT Customers.Name, Transaction.Amount, Transaction.Date
FROM Customers RIGHT OUTER JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

A LEFT OUTER JOIN retrieves data that is common in both tables AND any records from table one (the one on the left hand side of the join) that doesn’t have a corresponding record in the right table.

The example below will return results for customers even if no transaction is associated to the customer.

SELECT Customers.Name, Transaction.Amount, Transaction.Date
FROM Customers LEFT OUTER JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

An INNER JOIN retrieves data that is common in both tables. I.e. In this case the join is on the DepartmentID field and results will be returned where that field is matched in both tables.

SELECT Employees.Name
FROM Departments
INNER JOIN Employees ON Departments.DepartmentID =
         Employees.DepartmentID
WHERE Departments.Department LIKE ‘%Personnel’

protected string HomeOpens()
    {
        string rtn = "";

        //Declare the opjects
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader r;

        //read the connection string from web.config
        string connectionString = ConfigurationManager.ConnectionStrings[
            "DefaultDatabase"].ConnectionString;

        //Initialize connection
        conn = new SqlConnection(connectionString);

        //create the command
        StringBuilder SQL = new StringBuilder();
        SQL.Append(" SELECT homeopens.opendate, homeopens.startTime, homeopens.endTime, homeopens.pid,  ");
        SQL.Append(" property.address1, property.address2, property.suburb, property.postcode, ");
        SQL.Append(" propertyImages.imageFileName ");
        SQL.Append(" FROM property");
        SQL.Append(" INNER JOIN homeopens ON homeopens.pid = property.pid");
        SQL.Append(" INNER JOIN propertyImages on propertyImages.pid = property.pid");
        SQL.Append(" WHERE propertyImages.imageNo = 1");
        SQL.Append(" AND homeopens.opendate > GETDATE()");
        SQL.Append(" ORDER BY homeopens.opendate, homeopens.startTime");

        comm = new SqlCommand(SQL.ToString(), conn);

        string openDate = "<ul style='color: red;'>";

        try
        {
            //open the connection
            conn.Open();
            r = comm.ExecuteReader();
            while (r.Read())
            {
                openDate = openDate + "<li>" + r.GetDateTime(0).ToString("dddd, d MMMM yyyy") +
                    " from " + r.GetString(1) +
                    " to " + r.GetString(2) + "</li>";
            }
            openDate = openDate + "</ul>";
            r.Close();
            rtn = openDate;
        }
        catch (Exception ex)
        {
            lblHomeopens.Text = ex.Message.ToString();
            PanelHomeopens.Visible = true;
        }
        finally
        {
            conn.Close();
        }

        return rtn;

    }

This is a much simpler way to get a random record in your DB.

SELECT TOP 1 someColumn
FROM someTable
ORDER BY NEWID()