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