Extract Numbers from String

Declare @s varchar(100),@result varchar(100)
set @s='lkjflkjd-sflsakjqj098yvak8751'
set @result=''
select @result=@result + (case when number like '[0-9]' then number else '' end)
from (select substring(@s,number,1) as number from (select number from master..spt_values where type='p' and number between 1 and len(@s)) as t) as t
select @result as only_numbers

Result:
0988751

determine the number of rows for each table in the db

-- i like this best
SELECT t.name, SUM(rows) AS Rows
FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN(0,1) and t.name like 'tbl%'
GROUP BY t.name


-- can be used but only 100 can be displayed
sp_msforeachtable 'select ''?'', count (*) from ?'

loop thru db tables, check if particular column exists, then update the existing column

declare @tables Table(
id int identity(1,1),
name varchar(200)
)
insert into @tables(name)
SELECT name FROM sysobjects WHERE type = 'U'
declare @sql_string nvarchar(4000)
declare @name varchar(200), @min int, @max int
select @min=1
select @max=MAX(id) from @tables
while(@min <= @max)
begin
select @name = name from @tables where id = @min
if (exists(select * from information_schema.columns where table_name = @name and (column_name='editBy')) and
exists(select * from information_schema.columns where table_name = @name and (column_name='editDate')))
begin
set @sql_string = 'Update ' + @name + ' SET editBy=''engine upgrade'', editDate=getdate()'
--print @name
exec sp_executesql @sql_string
end
set @min = @min + 1
end

List all stored procs in the database

SELECT * FROM sys.procedures

Note: it works only in SQL Server 2005 and above

Delete all data from the database

1. Run this script:
SELECT 'DELETE FROM ' + name FROM sysobjects WHERE type = 'U'

2. Copy the result in the query window; then, execute
DELETE FROM tblAccommodationProductFacilityCategory
DELETE FROM tblProductClass
DELETE FROM tblVehicleDriveType
DELETE FROM tblCommunicationType
DELETE FROM tblAccommodationProductFacility
DELETE FROM tblVehicleRadioType
DELETE FROM tblAccommodationProductFacilityDescription
DELETE FROM tblVehicleType
...

3. Repeat over and over again until there is no error remaining

Retrieve XML data via Sql Server

ALTER PROCEDURE [dbo].Select_AccommodationMediaTypeSupplierByIds (@idsXML varchar(5000))
AS
DECLARE @ids int
EXEC sp_xml_preparedocument @ids OUTPUT, @idsXML
SELECT DISTINCT SupplierAccommodationMedia.*
FROM SupplierAccommodationMedia
WHERE SupplierAccommodationMedia.accommodationMediaTypeId IN (SELECT * FROM OpenXml(@ids,'/ids/id') WITH (v INT))
EXEC sp_xml_removedocument @ids

More info:
http://dotnetslackers.com/articles/xml/Dynamic_XML_From_SQL_Server.aspx
http://msdn.microsoft.com/en-us/library/ms178088.aspx
http://msdn.microsoft.com/en-us/library/ms186918.aspx

Display schema and constraints

SELECT C.CONSTRAINT_NAME ,PK.TABLE_NAME as PK_TABLE_NAME , PK.COLUMN_NAME as PK_COLUMN_NAME
, FK.TABLE_NAME as FK_TABLE_NAME, FK.COLUMN_NAME as FK_COLUMN_NAME
FROM Information_Schema.REFERENTIAL_CONSTRAINTS C
JOIN Information_Schema.KEY_COLUMN_USAGE PK
ON PK.CONSTRAINT_NAME = C.UNIQUE_CONSTRAINT_NAME
JOIN Information_Schema.KEY_COLUMN_USAGE FK
ON FK.CONSTRAINT_NAME = C.CONSTRAINT_NAME
--WHERE (((PK.TABLE_NAME ='Customers') and (FK.TABLE_NAME ='Orders'))
--OR ((PK.TABLE_NAME ='Orders') and (FK.TABLE_NAME ='Customers')))
Select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME from Information_Schema.KEY_COLUMN_USAGE where
TABLE_NAME = 'Orders'
Select CONSTRAINT_NAME, UNIQUE_CONSTRAINT_NAME from Information_Schema.REFERENTIAL_CONSTRAINTS
select * from INFORMATION_SCHEMA.Columns where Table_Name = 'Orders'

Display the description of tables and columns

select *
from sys.tables t
left outer join sys.extended_properties x on x.major_id = t.object_id
order by t.name

Ado.Net Transaction

Here's a sample use:

Private Sub ImportCsv()
Try
Dim con As SqlConnection = Connection
'Dim intResult As Integer
'Dim ctr As Integer
Dim fileToRead As String = brw_file.PostedFile.FileName
Dim fileStream As StreamReader
fileStream = File.OpenText(fileToRead)
Dim readContents As String
readContents = fileStream.ReadToEnd().Trim
Dim rowDelimiter As String
rowDelimiter = Chr(13) & Chr(10) '","
Dim rows As Array = Split(readContents, rowDelimiter)
Dim i As Integer
If IsValidCsv(readContents) = False Then
lblMessage.Text = message
Else
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
tr = con.BeginTransaction
For i = 0 To UBound(rows)
SaveToDb(con, tr, rows(i).ToString.Trim())
Next
tr.Commit()
Catch ex As Exception
tr.Rollback()
lblMessage.Text = ex.Message
End Try
If con.State = ConnectionState.Open Then
con.Close()
End If
End If
fileStream.Close()
Catch ex As Exception
'DotNetNuke.Services.Exceptions.LogException(ex)
lblMessage.Text = ex.Message
End Try


Sub SaveToDb(ByVal con As SqlConnection, ByVal tr As SqlTransaction, ByVal serial As String)
Dim intResult As Integer
Dim ctr As Integer
Dim cmd As SqlCommand = tr.Connection.CreateCommand
cmd.Transaction = tr
cmd.CommandText = "Insert Into Product (SerialNumber) Values (@SerialNumber)"
cmd.CommandType = CommandType.Text
Dim params As SqlParameter
params = New SqlParameter("@SerialNumber", SqlDbType.VarChar, 255)
params.Value = serial
cmd.Parameters.Add(params)
intResult = cmd.ExecuteNonQuery

If intResult > 0 Then
lblMessage.Text = "Records have been added to the database."
End If

End Sub

Import CSV to SQL

Not: The ff. was coded using VS 2003:

Private Sub ImportCsv()
Dim con As SqlConnection
Dim intResult As Integer
Dim ctr As Integer
Dim fileToRead As String = brw_file.PostedFile.FileName
Dim fileStream As StreamReader
fileStream = File.OpenText(fileToRead)
Dim readContents As String
readContents = fileStream.ReadToEnd().Trim
Dim rowDelimiter As String
rowDelimiter = ","
Dim rows As Array = Split(readContents, rowDelimiter)
Dim i As Integer
If IsValidCsv(readContents) = False Then
lblMessage.Text = "Invalid CSV file"
Else

For i = 0 To UBound(rows)
'Response.Write(rows(i).ToString.Trim & "
")
con = Connection
Dim cmd As SqlCommand = con.CreateCommand()
con.Open()
cmd.CommandText = "Insert Into NEO_ProductSerialNumbers (SerialNumber) Values (@SerialNumber)"
cmd.CommandType = CommandType.Text
Dim params As SqlParameter
params = New SqlParameter("@SerialNumber", SqlDbType.VarChar, 255)
params.Value = rows(i).ToString.Trim()
cmd.Parameters.Add(params)
cmd.ExecuteNonQuery
Next

If con.State = ConnectionState.Open Then
con.Close()
End If
End If
fileStream.Close()
End Sub

Function IsValidCsv(ByVal contents As String) As Boolean
'Any validation the user needs
If (contents.IndexOf(" ") > -1) Then
Return False
Exit Function
End If
If (contents.IndexOf("NULL") > -1) Then
Return False
Exit Function
End If
If (contents.IndexOf(",") = -1) Then
Return False
Exit Function
End If
If contents.StartsWith(",") Or contents.EndsWith(",") Then
Return False
Exit Function
End If
Return True
End Function
End Class