Copy Table Contents

USE Budget
GO

/*

DROP TABLE #columnsPerTable

*/

SET NOCOUNT ON

DECLARE @iter INT = 0, @iterCol INT = 0, @tableCount INT = 0, @columnCount INT = 0

DECLARE @insert VARCHAR(1024) = ' INSERT '
, @delete VARCHAR(1024) = ' DELETE '
, @select VARCHAR(1024) = ' SELECT '
, @from VARCHAR(1024) = ' FROM '
, @where VARCHAR(1024) = ' WHERE FiscalYear = @fromYear'
, @tableName VARCHAR(1024) = ''
, @columnName VARCHAR(1024) = ''
, @columnString VARCHAR(8000) = ''
, @comma CHAR(2)= ', '

-- CREATE TABLE #tables (
DECLARE @tables TABLE (
ident INT IDENTITY (1, 1)
, TABLE_SCHEMA VARCHAR (255)
, TABLE_NAME VARCHAR (255)
)

-- CREATE TABLE #columns (
DECLARE @columns TABLE (
TABLE_SCHEMA VARCHAR (255)
, TABLE_NAME VARCHAR (255)
, COLUMN_NAME VARCHAR (255)
)

CREATE TABLE #columnsPerTable (
-- DECLARE #columnsPerTable TABLE (
ident INT IDENTITY (1, 1)
, TABLE_NAME VARCHAR (255)
, COLUMN_NAME VARCHAR (255)
)

INSERT @tables (
TABLE_SCHEMA
, TABLE_NAME
)
SELECT DISTINCT
t.TABLE_SCHEMA
, t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG = t.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_CATALOG = 'Budget'
AND t.TABLE_SCHEMA = 'dbo'
AND t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME NOT LIKE '%bak%'
AND c.COLUMN_NAME LIKE '%fisc%'

-- # of tables
SELECT @tableCount = @@ROWCOUNT

INSERT @columns (
TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
)
SELECT t.TABLE_SCHEMA
, t.TABLE_NAME
, c.COLUMN_NAME
FROM @tables t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME

WHILE (@iter < @tableCount)
BEGIN

SELECT @tableName = TABLE_NAME
FROM @tables
WHERE ident = @iter + 1

INSERT #columnsPerTable (
TABLE_NAME
, COLUMN_NAME
)
SELECT TABLE_NAME
, COLUMN_NAME
FROM @columns
WHERE TABLE_NAME = @tableName

SELECT @columnCount = @@ROWCOUNT

WHILE (@iterCol < @columnCount)
BEGIN

SELECT @columnString += '[' + COLUMN_NAME + ']' + @comma
FROM #columnsPerTable
WHERE ident = @iterCol + 1
AND COLUMN_NAME IS NOT NULL

SELECT @iterCol += 1

END

SELECT @columnString = LEFT (RTRIM(LTRIM(@columnString)), LEN(RTRIM(LTRIM(@columnString))) - 1)

SELECT @delete
+ ' '
+ @tableName
+ @where

SELECT @insert
+ @tableName
+ ' ( '
+ @columnString
+ ' ) '

SELECT @select
+ @columnString
+ @from
+ @tableName
+ @where

SELECT @columnCount = 0, @iterCol = 0, @columnString = ''
SELECT @iter += 1
DELETE #columnsPerTable
DBCC CHECKIDENT (#columnsPerTable, RESEED, 0)

END

/*

DROP TABLE #columnsPerTable

DECLARE @insert VARCHAR(1024) = ' INSERT '
, @select VARCHAR(1024) = ' SELECT '
, @where VARCHAR(1024) = ' WHERE '
, @tableName VARCHAR(1024)
, @columnName VARCHAR(1024)
, @columnString VARCHAR(Max)
, @comma CHAR(2)= ', '

*/

Find Tables with Columns ...

SELECT DISTINCT t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
, c.COLUMN_NAME
, ' -- '
, t.TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG = t.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_CATALOG = 'Budget'
AND t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME NOT LIKE '%bak%'
AND c.COLUMN_NAME LIKE '%fisc%'

Table Sizes

-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

UPDATE #t SET reserved = REPLACE(REPLACE(reserved, 'kb', '') , ' ', '')
UPDATE #t SET data = REPLACE(REPLACE(data, 'kb', '') , ' ', '')
UPDATE #t SET index_size = REPLACE(REPLACE(index_size, 'kb', '') , ' ', '')
UPDATE #t SET unused = REPLACE(REPLACE(unused, 'kb', '') , ' ', '')


SELECT *
FROM #t
order by cast (data as int ) desc


SELECT *
FROM #t
order by cast (reserved as int ) desc

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #t

-- DROP TABLE #t

SQL SERVER CREATE READONLY USERS

/*********************************************************
Joe Kelly
2011-01-05 13:19:07.680

Add user to all non-system, writable, databases as READONLY

Input: db server username

*********************************************************/

SET NOCOUNT ON

DECLARE @userName VARCHAR (128) = 'UCSFMC\sosas'

DECLARE @dbList TABLE (ident INT IDENTITY (0, 1), dbName VARCHAR (128))
DECLARE @execString VARCHAR (128) = ''
, @aVeryLongString VARCHAR (MAX) = ''
, @lineFeed CHAR (4) = CHAR(10) + CHAR(13)
, @maxCount INT = 0
, @iter INT = 0

IF NOT EXISTS
(
SELECT name
FROM master.dbo.syslogins
WHERE name = @userName
)
BEGIN
SELECT 'Please create server login for: ' + @userName
RETURN
END

INSERT @dbList (dbName)
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('Master', 'Resource', 'TempDB', 'Model', 'MSDB', 'Distribution', 'ReportServer', 'ReportServerTempDB')
AND name NOT LIKE 'ASPState%'

SELECT @maxCount = @@ROWCOUNT

WHILE (@iter < @maxCount)
BEGIN
SELECT @aVeryLongString = 'USE ['
+ dbName
+ '] '
+ @lineFeed
+ 'GO '
+ @lineFeed
FROM @dbList
WHERE ident = @iter

SELECT @aVeryLongString += 'IF NOT EXISTS ( SELECT name FROM sysusers WHERE name = '''
+ @userName
+ ''') BEGIN EXEC sp_adduser '''
+ @userName
+ '''; END '
+ @lineFeed

SELECT @aVeryLongString += 'EXEC sp_addrolemember db_datareader, '''
+ @userName
+ '''; '
+ @lineFeed

SELECT @aVeryLongString += 'EXEC sp_addrolemember db_denydatawriter, '''
+ @userName
+ '''; '
+ @lineFeed

SELECT @aVeryLongString

SELECT @iter += 1
END

Generate Scripts for Finding Fragmentation

SELECT 'dbcc showcontig (' +
CONVERT(varchar(20),i.id) + ',' + -- table id
CONVERT(varchar(20),i.indid) + ') -- ' + -- index id
object_name(i.id) + '.' + -- table name
i.name -- index name
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.type = 'U'
and i.indid < 2
and i.id = object_id(o.name)
ORDER BY
object_name(i.id), i.indid