Function to return a table from a delimitted (csv) string

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_ParseCSVString]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_ParseCSVString]
GO
create function fn_ParseCSVString
(
@CSVString varchar(8000) ,
@Delimiter varchar(10)
)
returns @tbl table (s varchar(1000))
as
/* select * from dbo.fn_ParseCSVString ('qwe,c,rew,c,wer', ',c,') */
begin
declare @i int ,
@j int
select @i = 1
while @i <= len(@CSVString)
begin
select @j = charindex(@Delimiter, @CSVString, @i)
if @j = 0
begin
select @j = len(@CSVString) + 1
end
insert @tbl select substring(@CSVString, @i, @j - @i)
select @i = @j + len(@Delimiter)
end
return
end
GO

Function to return all non-alphameric characters from a string in a table.

create function GetCtrl
(@str varchar(1000))
returns @t table (offset int, chr int)
as
begin
declare @i int, @j int
select @i = 1, @j = 1
while @i <= len(@str)
begin
select @j = @i
select @i = patindex('%[^a-zA-Z0-9 ]%', substring(@str,@i,len(@str)-@i+1))
if @i = 0
set @i = len(@str) + 10
else
begin
select @i = @i + @j -1
insert @t select @i, ascii(substring(@str,@i,1))
end
select @i = @i + 1
end
return
end
select * from dbo.GetCtrl ('sad%sa*s(sdfg*')
i chr
----------- -----------
4 37
7 42
9 40
14 42

Returns the number of rows in each table in a database.

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_GetRowsForAllTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetRowsForAllTables]

GO

Create Procedure sp_GetRowsForAllTables
@DBName varchar(128) = null
as
set nocount on
if @DBName is null
set @DBName = db_name()
create table #a (TableName varchar(128), norows int null, id int identity(1,1))
declare @id int ,
@maxID int ,
@TableName varchar(128) ,
@FKName varchar(128) ,
@cmd nvarchar(1000) ,
@rc int,
@spcmd varchar(1000)
set @cmd = 'exec ' + @DBName + '..sp_executesql N''insert #a (TableName) select TABLE_NAME from information_schema.tables where TABLE_TYPE = ''''BASE TABLE'''' '' '

exec (@cmd)

select @id = 0 , @maxID = max(id) from #a
while @id < @maxID begin select @id = min(id) from #a where id > @id

select @TableName = TableName from #a where id = @id

set @cmd = 'exec ' + @DBName + '..sp_executesql N''update #a

set norows = (select rows from sysindexes where indid in (0,1) and id = object_id(''''' + @TableName + '''''))'

set @cmd = @cmd + ' where #a.id = ' + convert(varchar(10),@id) + '''' exec (@cmd)

if @rc <> 0 or @@error <> 0
begin
raiserror('failed %s',16,-1,@TableName)
return
end
end
select * from #a
drop table #ago