why is USA addicted to oil ...

Watch CBS Videos Online>

how-to backup all db objects changes in a change log table on sql server 2005

USE [MyDb]
GO
/****** Object: Table [tbDataMeta_ChangeLog] Script Date: 12/12/2008 23:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbDataMeta_ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](256) NOT NULL,
[EventType] [varchar](50) NOT NULL,
[ObjectName] [varchar](256) NOT NULL,
[ObjectType] [varchar](25) NOT NULL,
[SqlCommand] [varchar](max) NOT NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
[LoginName] [varchar](256) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [MyDb]
GO
/****** Object: DdlTrigger [trig_BackUpDbObjects] Script Date: 12/12/2008 22:54:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create trigger [trig_BackUpDbObjects]
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as

set nocount on

declare @data xml
set @data = EVENTDATA()

insert into [dbo].[tbDataMeta_ChangeLog](databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)



GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trig_BackUpDbObjects] ON DATABASE

from sp_gen get sql server all meta data

SELECT Cols.TABLE_NAME,
Cols.COLUMN_NAME,
Cols.ORDINAL_POSITION,
Cols.DATA_TYPE,
Cols.NUMERIC_PRECISION,
Cols.NUMERIC_SCALE,
Cols.IS_NULLABLE,
Cols.CHARACTER_MAXIMUM_LENGTH,
COLUMNPROPERTY(object_id(Cols.TABLE_NAME), Cols.COLUMN_NAME, 'IsIdentity') AS IsIdentity,
( SELECT COUNT(KCU.COLUMN_NAME)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.TABLE_NAME = TC.TABLE_NAME
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE KCU.TABLE_NAME = Cols.TABLE_NAME
AND KCU.COLUMN_NAME = Cols.COLUMN_NAME
) AS IsIndex
FROM [INFORMATION_SCHEMA].[COLUMNS] Cols
ORDER BY Cols.TABLE_NAME,
Cols.ORDINAL_POSITION

Cool programs and tools - most of which free (as beer)

• Opera - simply the best browser on the planet
• 
  
• firefox the most extensible browser


• Mozilla Thunderbird
• 
  
• Textpad - the best text editor for Windows








smstoolpack - create CRUD for sql server
GreenShot
Open diff - tool for comparing sql server 2005 databases

Knowledge sharing can solve all humanity problems and wikipedia is the best platform making it possible ... so SUPPORT IT !!!

Drop all formatter extended props from the stored procedures of sql server 2005

USE mydb
GO
alter proc procUtils_DeleteProcsExtProps
as
begin
--use poc
--go
DECLARE @procName varchar(100)
DECLARE @cursorProcNames CURSOR
SET @cursorProcNames = CURSOR FOR
select s.name from sysobjects s where type = 'P' AND S.NAME NOT LIKE '%sp_alterdiagram' and s.name not like '%sp_alterdiagram' and s.name not like '%sp_renamediagram' and s.name not like '%sp_dropdiagram' and s.name not like '%sp_helpdiagrams' and s.name

not like '%sp_creatediagram' and s.name not like '%sp_upgraddiagrams' and s.name not like '%sp_helpdiagramdefinition%'

OPEN @cursorProcNames
FETCH NEXT
FROM @cursorProcNames INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN

set nocount off;
-- exec sp_HelpText @procName --- or print them
-- print @procName

print 'EXEC sys.sp_dropextendedproperty @name=N''Col0NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col1NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col2NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col3NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col4NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col5NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col6NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col7NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col8NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col9NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col10NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col11NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col12NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col13NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col14NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col15NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col16NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col17NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col18NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col19NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''
print 'EXEC sys.sp_dropextendedproperty @name=N''Col20NumberFormat'', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + ''''




FETCH NEXT
FROM @cursorProcNames INTO @procName
END
CLOSE @cursorProcNames
end


/*


USAGE:

EXEC procUtils_DeleteProcsExtProps
GO


*/

print all user defined stored procedure in sql server using cursor

DECLARE @procName varchar(100)
DECLARE @cursorProcNames CURSOR
SET @cursorProcNames = CURSOR FOR
select s.name from sysobjects s where type = 'P' AND S.NAME NOT LIKE '%sp_alterdiagram' and s.name not like '%sp_alterdiagram' and s.name not like '%sp_renamediagram' and s.name not like '%sp_dropdiagram' and s.name not like '%sp_helpdiagrams' and s.name not like '%sp_creatediagram' and s.name not like '%sp_upgraddiagrams' and s.name not like '%sp_helpdiagramdefinition%'

OPEN @cursorProcNames
FETCH NEXT
FROM @cursorProcNames INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN

set nocount off;
-- exec sp_HelpText @procName --or show their text
print @procName

FETCH NEXT
FROM @cursorProcNames INTO @procName
END
CLOSE @cursorProcNames

DEALLOCATE @cursorProcNames