COOL SP: Export data into SQL script

You prabobly can find many versions of this kind of SP. It takes table name and return SQL script which will fill table with data.
It has one extra flag which change script form

INSERT INTO .. VALUES (..)
INSERT INTO .. VALUES (..)
..

TO

INSERT INTO .. SELECT ..
UNION ALL
SELECT ..
UNION ALL
SELECT ..
..

I'm not shure but in Microsoft SQL Server 2008 this feature is buildin.
The code:

IF object_id(N'dbo.TECH_ScriptData', N'P') IS NOT NULL DROP PROCEDURE dbo.TECH_ScriptData
GO

CREATE PROCEDURE dbo.TECH_ScriptData
@tabName varchar(100), -- table to script data
@ex bit = 0 -- if 1 (smaller script, but ) = INSERT INTO .. SELECT ... UNION ALL SELECT ... UNION ALL
-- if 0 = INSERT INTO .. INSERT INTO ..
AS
BEGIN
SET NOCOUNT ON

DECLARE @s nvarchar(max)
DECLARE @sql nvarchar(max)
DECLARE @cols nvarchar(max)
DECLARE @ins nvarchar(max)

SET @cols = ''
SET @ins = ''

------------------------------------------------------
-- Genereate column list
SELECT @cols = @cols + column_name+', ',
@ins = @ins + CASE WHEN data_type IN ('uniqueidentifier','varchar', 'nvarchar', 'nchar', 'char', 'datetime')
THEN '''''''''+'+CASE WHEN data_type = 'uniqueidentifier'
THEN 'CAST('+column_name+' as varchar(50))'
ELSE column_name END+'+'''''''''
ELSE 'CAST('+column_name+' as varchar(50))'
END+' + '','' + '
FROM information_schema.columns
WHERE table_name = @tabName AND COLUMNPROPERTY( OBJECT_ID(@tabName),column_name,'IsIdentity') = 0 -- we dont wont identity columns
ORDER BY ORDINAL_POSITION
SET @s = 'INSERT INTO '+@tabName+' ('+LEFT(@cols, LEN(@cols)-2)+') '

------------------------------------------------------
-- Genereate data
SET @sql = 'SELECT '+LEFT(@ins, LEN(@ins)-9)+' FROM '+@tabName

CREATE TABLE #tt_vals (vals varchar(max))
INSERT INTO #tt_vals
EXEC (@sql)

IF @ex = 0
BEGIN
SELECT @s+' VALUES ('+vals+')' FROM #tt_vals
END ELSE
BEGIN
--SET @s = @s + char(13) + char(10)
PRINT @s
SELECT 'UNION ALL'+char(13)+char(10)+' SELECT '+vals FROM #tt_vals

--PRINT LEFT(@s, LEN(@s)-11)
END

END
GO

COOL SP: Get multiple table size with filter on table name

U can find many versions of this kind of SP in the Internet.
It will return a rowset with table name, table data sizes, sizes of indexes etc.
All (except row count)) in Kilobytes.
The reason why I wrote another version of this SP is my version do not use sp_spaceused system procedure. It also free of loops and cursors.
Its fast, it can filter table names, and by default it do not show empty tables.
I use it to check table size in KB versus row count. It really helps when U have limited DB size.


IF object_id(N'dbo.TECH_MassSpaceUsed', N'P') IS NOT NULL DROP PROCEDURE dbo.TECH_MassSpaceUsed
GO

CREATE PROCEDURE dbo.TECH_MassSpaceUsed
@filter varchar(100) = '',
@showNoEmpty bit = 1
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #tt_sizes (obId int, resPage int, usedPage int, page int, rCount int)

INSERT INTO #tt_sizes
select p.object_id, sum(a.total_pages), sum(a.used_pages),
sum(CASE
When a.type <> 1 Then a.used_pages
When p.index_id <>
Else 0
END),
sum(CASE
When (p.index_id < type =" 1)">
Else 0
END)
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id = p.object_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE o.type = 'U ' AND (ISNULL(@filter, '') = '' OR o.name LIKE @filter)
GROUP BY p.object_id


UPDATE s
SET resPage = it.resPage,
usedPage = it.usedPage
FROM #tt_sizes s
INNER JOIN (
SELECT it.parent_id obId, sum(a.total_pages) resPage, sum(a.used_pages) usedPage
FROM sys.internal_tables it
INNER JOIN sys.partitions p ON p.object_id = it.object_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id
) it ON s.obId = it.obId


SELECT table_name = object_name(obId),
rows = convert(char(11), rCount),
reserved_in_kb = ltrim(str(resPage * 8192 / 1024.,15,0)),
data_in_kb = ltrim(str(page * 8192 / 1024.,15,0) ),
index_size_in_kb = ltrim(str((usedPage - page) * 8192 / 1024.,15,0) ),
unused_in_kb = ltrim(str((resPage - usedPage) * 8192 / 1024.,15,0))
FROM #tt_sizes
WHERE @showNoEmpty = 0 OR resPage > 0
ORDER BY resPage DESC

END
go


This SP is rewriten version of sp_spaceused sys SP. It uses same queries but i do not filter data for only one table. Thats all.

COOL SP: Get comma separated column names

This one is really useful.
There is one parameter where U set table name.
Result is in messages tab, so don't expect a resultset.


IF object_id(N'dbo.TECH_Columns', N'P') IS NOT NULL DROP PROCEDURE dbo.TECH_Columns
GO

CREATE PROCEDURE dbo.TECH_Columns
@table varchar(100) = ''
AS
BEGIN
SET NOCOUNT ON

DECLARE @s nvarchar(max)
SET @s = ''
SELECT @s = @s + column_name+', '
FROM information_schema.columns
WHERE table_name LIKE @table
ORDER BY ordinal_position

PRINT LEFT(@s, LEN(@s)-2)
END
GO

This Stored Procedure uses Information schema view: information_schema.columns to retrieve columns for table. Another way is to use system view sys.all_columns but information_schema views are more human readable.

COOL SP: Mass count - get count(*) from multiple tables with table name filtering

This is first of my COOL SP :)
It will get U list of table name with number of rows in that table.
It has a table name filtering based on T-SQL's LIKE key-word.
It uses dynamic SQL feature (sp_executeSql) to execute query concated in first select statment

Here it is:

IF object_id(N'dbo.TECH_MassCount', N'P') IS NOT NULL DROP PROCEDURE dbo.TECH_MassCount
GO

CREATE PROCEDURE dbo.TECH_MassCount
@filter varchar(100) = ''
AS
BEGIN
SET NOCOUNT ON

DECLARE @s nvarchar(max)
SET @s = ''

SELECT @s = @s + 'SELECT '''+table_name +''' as tabName, COUNT(*) as cnt FROM '+table_name+' UNION ALL '
FROM information_schema.tables
WHERE @filter = '' OR table_name LIKE @filter
ORDER BY table_name

IF ISNULL(@s, '')=''
RETURN -1
SET @s = LEFT(@s, LEN(@s)-11)
EXEC sp_executeSql @s
END
GO

How to use it:
EXEC TECH_MassCount
OR
EXEC TECH_MassCount 'tablename%' -- if U wont to filter output, and speed up query

Now few words about the SP.