MSSQL Column info

SELECT * FROM information_schema.columns

Till Next Time

MSSQL Update based on other table

UPDATE Table1
SET Table1.Column1 = Table2.Column1
FROM Table1 INNER JOIN
Table2 ON
Table1.ColumnKey = Table2.ColumnKey
Till Next Time

MSSQL Scripted indexes

Dimension tables:

/*
(re)creating all dimensional indexes
within a schema in one go
for dimension tables
20081219 JJM: First
darft
*/
declare @strSql01 varchar(4000)
declare @strSql02
varchar(4000)
declare @strTableName varchar(500)
declare @strColumnName
varchar(500)
declare @strIndexFileGroup varchar(500)
declare
@strSchemaName varchar(500)
set @strIndexFileGroup = 'INDEX'
set
@strSchemaName = 'dbo'
declare idx_cur cursor for
SELECT distinct
table_name, column_name FROM information_schema.columns
WHERE Table_Name
like 'Dim_%'
and Column_name like '%Code%'
and table_schema =
@strSchemaName
and table_name in (SELECT name FROM sys.objects WHERE name
like 'Dim_%'
AND type in (N'U')
) order by table_name
open
idx_cur
fetch next from idx_cur into @strTableName,@strColumnName
while
@@fetch_status=0
begin
set @strSql01 = 'IF EXISTS (SELECT * FROM
sys.indexes WHERE object_id =
OBJECT_ID(N''['+@strSchemaName+'].['
+@strTableName
+']'') AND name =
N''IX_'
+@strTableName +'_' +@strColumnName +''')
DROP INDEX
[IX_'
+@strTableName +'_' +@strColumnName +'] ON
['+@strSchemaName+'].['
+@strTableName
+'] WITH ( ONLINE = OFF
)'
print @strSql01
exec (@strSql01)
set @strSql02 = 'CREATE
NONCLUSTERED INDEX [IX_'
+@strTableName +'_' +@strColumnName +'] ON
['+@strSchemaName+'].['
+@strTableName
+'] ([' + @strColumnName
+']
ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, '
+ 'SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, '
+'ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ['+ @strIndexFileGroup
+']'

print @strSql02
exec (@strSql02)
fetch next from idx_cur
into @strTableName,@strColumnName
end
close idx_cur
deallocate
idx_cur

Fact tables:

/*
(re)creating all dimensional indexes within a schema in one go
for facttables
20081219 JJM: First darft
*/
declare @strSql01 varchar(4000)
declare @strSql02 varchar(4000)
declare @strTableName varchar(500)
declare @strColumnName varchar(500)
declare @strIndexFileGroup varchar(500)
declare @strSchemaName varchar(500)

set @strIndexFileGroup = 'INDEX'
set @strSchemaName = 'dbo'

declare idx_cur cursor for
SELECT distinct table_name, column_name FROM information_schema.columns
WHERE Table_Name like 'Feit_%'
and Column_name like 'Dim_%'
and table_schema = @strSchemaName
and table_name in (SELECT name FROM sys.objects WHERE name like 'Feit_%'
AND type in (N'U')
) order by table_name

open idx_cur
fetch next from idx_cur into @strTableName,@strColumnName
while @@fetch_status=0
begin

set @strSql01 = 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''['+@strSchemaName+'].['
+@strTableName
+']'') AND name = N''IX_'
+@strTableName +'_' +@strColumnName +''')
DROP INDEX [IX_'
+@strTableName +'_' +@strColumnName +'] ON ['+@strSchemaName+'].['
+@strTableName
+'] WITH ( ONLINE = OFF )'

print @strSql01
exec (@strSql01)

set @strSql02 = 'CREATE NONCLUSTERED INDEX [IX_'
+@strTableName +'_' +@strColumnName +'] ON ['+@strSchemaName+'].['
+@strTableName
+'] ([' + @strColumnName
+'] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, '
+ 'SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, '
+'ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ['+ @strIndexFileGroup +']'


print @strSql02
exec (@strSql02)

fetch next from idx_cur into @strTableName,@strColumnName
end

close idx_cur
deallocate idx_cur

Till Next Time

MSSQL Rowcount + trending

/* AUTOMATIC TABLE COUNT AND TREND LOGGING */
/* John Minkjan Ciber Nederland */
/* 20081205 v001 intial version */
/* Decalartions */
DECLARE @table_name varchar(250)
DECLARE @strSQL varchar(500)
Declare @SQL varchar(2000)
DECLARE @TABLE_COUNT INT
Declare @TableCount_ID Int
declare @TableCountTrend varchar(50)
/* Part 1 Get the counts */
DECLARE table_cursor CURSOR FOR
SELECT name FROM sys.objects
WHERE type in (N'U')
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'DECLARE Dyn_cursor CURSOR
FOR SELECT COUNT(*) FROM ' + @table_name
Exec(@SQL)
OPEN Dyn_cursor
FETCH NEXT FROM Dyn_cursor
INTO @TABLE_COUNT
CLOSE Dyn_cursor
DEALLOCATE Dyn_cursor
INSERT INTO [XXXXXX].[dbo].[AUD_TableCount]
([TableName]
,[TableCountDate]
,[TableCount])
VALUES
(@table_name
,GETDATE()
,@TABLE_COUNT)
FETCH NEXT FROM table_cursor
INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
/* Part 2 Set the trends */
DECLARE TREND_CURSOR CURSOR FOR
(select t1.TableCount_ID ,
case when t1.tablecount - t2.tablecount = 0 then 'NONE' ELse
case when t1.tablecount - t2.tablecount > 0 then 'UP' else 'DOWN' end end
as TableCountTrend from
(SELECT TableCount_ID,
TableName,
TableCountDate,
TableCount,
ROW_NUMBER() OVER(PARTITION BY TableName ORDER BY TableCountDate ASC) RowVersion
FROM [AMIGO_LOG].[dbo].[AUD_TableCount]) T1,
(SELECT TableName,
TableCountDate,
TableCount,
ROW_NUMBER() OVER(PARTITION BY TableName ORDER BY TableCountDate ASC) RowVersion
FROM [AMIGO_LOG].[dbo].[AUD_TableCount]) T2
where
t1.tablename = t2.tablename and
t1.rowversion-1 = t2.rowversion and
t1.TableCountDate >= getdate()-90)
OPEN TREND_CURSOR
FETCH NEXT FROM TREND_CURSOR
INTO @tableCount_ID, @TableCountTrend
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM TREND_CURSOR
INTO @tableCount_ID, @TableCountTrend
UPDATE [AMIGO_LOG].[dbo].[AUD_TableCount]
SET [TableCountTrend] = @TableCountTrend
WHERE TableCount_ID= @tableCount_ID
END
CLOSE TREND_CURSOR
DEALLOCATE TREND_CURSOR

till next time

MSSQL Q&D Rowcount

select Distinct
object_name(id)
, rowcnt
from
sysindexes
where object_name(id) not like 'sys%'

Till Next Time

MSSQL Using a dynamic cursor

Use Pubs
Declare @tblName Varchar(40)
Declare @SQL varchar(2000)

Set @tblName = 'Authors'
SET @SQL = 'DECLARE Dyn_cursor CURSORFOR SELECT * FROM ' + @tblName

Exec(@SQL)

Open Dyn_Cursor


Close Dyn_cursor

Deallocate Dyn_cursor

Till Next Time