Created Stored Procedure

• Lester Policarpio

  SSCertifiable

  Points: 7109

  More actions

  March 5, 2008 at 5:37 pm

  #785835

  Try this....

  after creating the table , definition given by you, i am running below:

  --This is for the creation of tables, views, sp etc. in all databases in the server

  DECLARE @db_names varchar(50)

  DECLARE @command varchar(2000)

  DECLARE @STR varchar(2000)

  DECLARE CLICS CURSOR FOR

  select name from master..sysdatabases order by name

  OPEN CLICS

  FETCH NEXT FROM CLICS INTO @db_names

  WHILE(@@FETCH_STATUS = 0)

  BEGIN

  --MONITORING OF CREATED TABLES,VIEWS,SP,FUNCTION,TRIGGER ETC.

  SET @command = 'select name as ''Name'',

  CASE WHEN xtype = ''S'' THEN ''System Tables''

  WHEN xtype = ''P'' THEN ''Stored Procedure''

  WHEN xtype = ''PK'' THEN ''Primary Key''

  WHEN xtype = ''U'' THEN ''User Table''

  WHEN xtype = ''D'' THEN ''Constraints''

  WHEN xtype = ''F'' THEN ''Foreign Key''

  WHEN xtype = ''FN'' THEN ''Function''

  WHEN xtype = ''V'' THEN ''Views''

  WHEN xtype = ''TR'' THEN ''Triggers''

  ELSE ''Unknown Table Type''

  END as ''Table Type'',convert(varchar(50),crdate) as ''Date Created'',

  uid as ''Database Owner ID'','+''''+@db_names+''''+' as ''Database Name'',

  @@servername as ''Server_Name''

  from '+'['+@db_names+']'+'..sysobjects

  where convert(char(8),crdate,112) >''20080209''--WHERE DATE GREATER THAN THIS. YOU CAN SET YOUR FILTER DATE

  and name not like ''dt_%''

  '

  SET @STR = 'INSERT INTO '+'model.dbo.Table_monitoring '+@command

  exec (@str)

  FETCH NEXT FROM CLICS INTO @db_names

  END

  CLOSE CLICS

  DEALLOCATE CLICS

  "-=Still Learning=-"

  Lester Policarpio