Simple log connections SQLServer 2000

Rédigé par M Sozezzo - - Aucun commentaire

This code create a table to log all connections.

It is very simple, it just logs when, who, from where and which database.

For this we use “master.dbo.sysprocesses”

 

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-2017


Create table to log on [Master] database.

 USE [master] GO CREATE TABLE [dbo].[dbaLogAccess] (     [dbaLogAccessId] [int] IDENTITY (1, 1) NOT NULL ,     [ConnectionDate] [datetime] NULL ,     [loginame] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,     [hostname] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,     [dbname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ) ON [PRIMARY] 

Script to add logs, you can run each minute to update the log. This script avoids to repeater, it only saves new connections. We recommend using a job and schedule each minute to run this script.

 INSERT INTO master.dbo.dbaLogAccess (ConnectionDate, loginame, hostname, dbname)   SELECT DISTINCT     tbNew.ConnectionDate,     tbNew.loginame,     tbNew.hostname,     tbNew.dbname   FROM (SELECT     CONVERT(char(10), GETDATE(), 126) AS ConnectionDate,     RTRIM(loginame) AS loginame,     hostname,     CASE       WHEN dbid = 0 THEN ''       WHEN dbid <> 0 THEN DB_NAME(dbid)     END AS dbname   FROM master.dbo.sysprocesses   WHERE (hostname <> '')) AS tbNew   LEFT OUTER JOIN master.dbo.dbaLogAccess AS tbOld     ON tbNew.ConnectionDate = tbOld.ConnectionDate     AND tbNew.loginame = tbOld.loginame     AND tbNew.hostname = tbOld.hostname     AND tbNew.dbname = tbOld.dbname   WHERE (tbOld.dbaLogAccessId IS NULL); 

This code limit connection by day, we can change by hour or by minute, you just change how many character you use it:
 print CONVERT(char(14), GETDATE(), 126) + '00:00'
print CONVERT(char(17), GETDATE(), 126) + '00'
print CONVERT(char(20), GETDATE(), 126)

Well, you may do not like to use [master] database, but it is the place to record the system-level information. Good news, you can easily change it.

or you can use : https://www.sqlshack.com/creating-successful-auditing-strategy-sql-server-databases/

 

 

 

SQL Server Sp_msforeachdb query character without limitation

Rédigé par M Sozezzo - - Aucun commentaire

When we write a query than is running for every database in the instance of SQL Server, we use Sp_msforeachdb.
 
If your query is bigger than 2000 chars, the query cannot work because Sp_msforeachdb has a character limitation of 2000 chars.

The solution is to re-create MySp_myforeachdb. We use sp_helptext to see the definition of sp_MSforeachdb and sp_MSforeach_worker, and we change to a new character limitation of 262144 chars.


Everything works as before but without character limitation of 2000 chars.


sp_Myforeachdb



USE master;
GO
/*
 * This is the worker proc for all of the "for each" type procs.  Its function is to read the
 * next replacement name from the cursor (which returns only a single name), plug it into the
 * replacement locations for the commands, and execute them.  It assumes the cursor "hCForEach***"
 * has already been opened by its caller.
 * worker_type is a parameter that indicates whether we call this for a database (1) or for a table (0)
 */
create proc dbo.sp_Myforeach_worker
 @command1 nvarchar(max), @replacechar nchar(1) = N'?', @command2 nvarchar(max) = null, @command3 nvarchar(max) = null, @worker_type int =1
as

 create table #qtemp ( /* Temp command storage */
  qnum    int    NOT NULL,
  qchar    nvarchar(max) COLLATE database_default NULL
 )

 set nocount on
 declare @name nvarchar(517), @namelen int, @q1 nvarchar(max), @q2 nvarchar(max)
   declare @q3 nvarchar(max), @q4 nvarchar(max), @q5 nvarchar(max)
 declare @q6 nvarchar(max), @q7 nvarchar(max), @q8 nvarchar(max), @q9 nvarchar(max), @q10 nvarchar(max)
 declare @cmd nvarchar(max), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(max)
   declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)

 declare @local_cursor cursor
 if @worker_type=1 
  set @local_cursor = hCForEachDatabase
 else
  set @local_cursor = hCForEachTable
 
 open @local_cursor
 fetch @local_cursor into @name

 /* Loop for each database */
 while (@@fetch_status >= 0) begin
  /* Initialize. */

      /* save the original dbname */
      select @namesave = @name
  select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)
  while (@cmd is not null) begin  /* Generate @q* for exec() */
   /*
    * Parse each @commandX into a single executable batch.
    * Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we'll need to allow overflow.
    * We also may append @commandX's (signified by '++' as first letters of next @command).
    */
   select @replacecharindex = charindex(@replacechar, @cmd)
   while (@replacecharindex <> 0) begin

            /* 7.0, if name contains ' character, and the name has been single quoted in command, double all of them in dbname */
            /* if the name has not been single quoted in command, do not doulbe them */
            /* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */
            select @name = @namesave
            select @namelen = datalength(@name)
            declare @tempindex int
            if (substring(@cmd, @replacecharindex - 1, 1) = N'''') begin
               /* if ? is inside of '', we need to double all the ' in name */
               select @name = REPLACE(@name, N'''', N'''''')
            end else if (substring(@cmd, @replacecharindex - 1, 1) = N'[') begin
               /* if ? is inside of [], we need to double all the ] in name */
               select @name = REPLACE(@name, N']', N']]')
            end else if ((@name LIKE N'%].%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [owner].[name] format, handle it */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @tempindex = charindex(N'].[', @name)
               select @nametmp  = substring(@name, 2, @tempindex-2 )
               select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )
               select @nametmp  = REPLACE(@nametmp, N']', N']]')
               select @nametmp2 = REPLACE(@nametmp2, N']', N']]')
               select @name = N'[' + @nametmp + N'].[' + @nametmp2 + ']'
            end else if ((@name LIKE N'%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [name] format, handle it */
       /* j.i.c., since we should not fall into this case */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @nametmp = substring(@name, 2, len(@name)-2 )
               select @nametmp = REPLACE(@nametmp, N']', N']]')
               select @name = N'[' + @nametmp + N']'
            end
            /* Get the new length */
            select @namelen = datalength(@name)

            /* start normal process */
    if (datalength(@cmd) + @namelen - 1 > 262144) begin
     /* Overflow; put preceding stuff into the temp table */
     if (@useq > 9) begin
      close @local_cursor
      if @worker_type=1 
       deallocate hCForEachDatabase
      else
       deallocate hCForEachTable
       
      RAISERROR(55555, 16, 1); -- N'sp_MSforeach_worker assert failed:  command too long'
      return 1
     end
     if (@replacecharindex < @namelen) begin
      /* If this happened close to beginning, make sure expansion has enough room. */
      /* In this case no trailing space can occur as the row ends with @name. */
      select @nextcmd = substring(@cmd, 1, @replacecharindex)
      select @cmd = substring(@cmd, @replacecharindex + 1, 262144)
      select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)
      select @replacecharindex = charindex(@replacechar, @cmd)
      insert #qtemp values (@useq, @nextcmd)
      select @useq = @useq + 1
      continue
     end
     /* Move the string down and stuff() in-place. */
     /* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */
     /* In this case, the char to be replaced is moved over by one. */
     insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))
     if (substring(@cmd, @replacecharindex - 1, 1) = N' ') begin
      select @cmd = N' ' + substring(@cmd, @replacecharindex, 262144)
      select @replacecharindex = 2
     end else begin
      select @cmd = substring(@cmd, @replacecharindex, 262144)
      select @replacecharindex = 1
     end
     select @useq = @useq + 1
    end
    select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
    select @replacecharindex = charindex(@replacechar, @cmd)
   end

   /* Done replacing for current @cmd.  Get the next one and see if it's to be appended. */
   select @usecmd = @usecmd + 1
   select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end
   if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N'++') begin
    insert #qtemp values (@useq, @cmd)
    select @cmd = substring(@nextcmd, 3, 262144), @useq = @useq + 1
    continue
   end

   /* Now exec() the generated @q*, and see if we had more commands to exec().  Continue even if errors. */
   /* Null them first as the no-result-set case won't. */
   select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null
   select @q1 = qchar from #qtemp where qnum = 1
   select @q2 = qchar from #qtemp where qnum = 2
   select @q3 = qchar from #qtemp where qnum = 3
   select @q4 = qchar from #qtemp where qnum = 4
   select @q5 = qchar from #qtemp where qnum = 5
   select @q6 = qchar from #qtemp where qnum = 6
   select @q7 = qchar from #qtemp where qnum = 7
   select @q8 = qchar from #qtemp where qnum = 8
   select @q9 = qchar from #qtemp where qnum = 9
   select @q10 = qchar from #qtemp where qnum = 10
   truncate table #qtemp
   exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)
   select @cmd = @nextcmd, @useq = 1
  end /* while @cmd is not null, generating @q* for exec() */

  /* All commands done for this name.  Go to next one. */
  fetch @local_cursor into @name
 end /* while FETCH_SUCCESS */
 close @local_cursor
 if @worker_type=1 
  deallocate hCForEachDatabase
 else
  deallocate hCForEachTable
  
 return 0

GO

GO
CREATE PROC sp_Myforeachdb
 @command1 nvarchar(max), @replacechar nchar(1) = N'?', @command2 nvarchar(max) = null, @command3 nvarchar(max) = null, 
 @precommand nvarchar(max) = null, @postcommand nvarchar(max) = null
 AS

 set deadlock_priority low 
     
 /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */ 
 /* @precommand and @postcommand may be used to force a single result set via a temp table. */ 
 
 /* Preprocessor won't replace within quotes so have to use str(). */ 
 declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12) 
 select @inaccessible = ltrim(str(convert(int, 0x03e0), 11)) 
 select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11)) 
 select @dbinaccessible = N'0x80000000'  /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */ 
 
 if (@precommand is not null) 
  exec(@precommand) 
 
 declare @origdb nvarchar(128) 
 select @origdb = db_name() 
 
 /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */ 
   /* Create the select */ 
 exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' + 
   N' where (d.status & ' + @inaccessible + N' = 0)' + 
   N' and (DATABASEPROPERTYEX(d.name, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess(d.name) = 1))' ) 
 
 declare @retval int 
 select @retval = @@error 
 if (@retval = 0) 
  exec @retval = dbo.sp_Myforeach_worker @command1, @replacechar, @command2, @command3, 1 
 
 if (@retval = 0 and @postcommand is not null) 
  exec(@postcommand) 
 
   declare @tempdb nvarchar(258) 
   SELECT @tempdb = REPLACE(@origdb, N']', N']]') 
   exec (N'use ' + N'[' + @tempdb + N']') 
 
 return @retval 
GO  

Reorganize or Rebuild Indexes

Rédigé par M Sozezzo - - Aucun commentaire

This script remedy index fragmentation by reorganizing or rebuilding an index.

Small tables, small indexes, low fragmentation, we do not care, and keep simple & easy

We are talking about the guidance which is:
•if a table has less than 10000 rows, to do nothing
•if an index has less than 1000 pages, to do nothing
•if the index has:
    less than 5% logical fragmentation, to do nothing
    between 5% and 30% logical fragmentation, reorganize it
    more than 30% logical fragmentation, rebuild it
• New FILLFACTOR = 98%


Why do we fix something we do not need it?
We should start to delete unused indexes if it is possible: [see Unused indexes]
•Unused index, delete it, when :
    - Nonclustered indexes
    - Non-primary keys
    - Non unique
    - Non-unused  (#Total read < 100 and #total write > 1000 )
    - Hypothetical Index.

 

Alternative solutions:
1. We can buy an application for index monitoring and analysis.
2. We can change the configuration. ex: MIXED_PAGE_ALLOCATION
3. We can create better indexes.
4. We can use this nice solution of Michelle Ufford [https://github.com/MichelleUfford/sql-scripts/tree/master/indexes]

 

 

Lire la suite de Reorganize or Rebuild Indexes

Search all tables, all columns for a specific value SQL Server

Rédigé par M Sozezzo - - Aucun commentaire

This script* search for a specific value that you can select sql type:

  • String - %my string%
  • Number - exact number
  • Date - exact format yyyy/MM/dd HH:mm:ss 
  • XML - %my string%

You can limit how many result by tables using "TOP n"

You must select database to run this script, and avoid using production environment.

* Tested with Sql Server 2012, 2014 and 2016

 

-- Set Parameters ------------

DECLARE @Search nvarchar(120) = '2008';

DECLARE @CheckString      INT = 1;
DECLARE @CheckNumber      INT = 0;
DECLARE @CheckDateTime    INT = 0;  -- cast(@castDateTime)   
DECLARE @CheckXml         INT = 0;


DECLARE @ReturnTop        NVARCHAR(50) = 'TOP 100';
DECLARE @castDateTime     NVARCHAR(50) = 'yyyy/MM/dd HH:mm:ss';  -- yyyy/MM/dd HH:mm:ss

-----------------------
SET NOCOUNT ON
PRINT '-- Server Name : ' + @@servername
PRINT '-- Database : ' + db_name()

BEGIN TRY DROP TABLE #ColumnCast; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
CREATE TABLE #Columncast ( [Typelist] nvarchar(MAX) , [Columncast] nvarchar(128) , [ColumnWhere] nvarchar(256) );
IF (@CheckString = 1)   BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|nvarchar|varchar|char|nchar|', '$ColumnName', ' $ColumnCast LIKE ''%$Search%''' ) END;
IF (@CheckNumber = 1)   BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|int|smallint|real|bigint|bigint|tinyint|float|bit|decimal|numeric|', 'CAST($ColumnName AS NVARCHAR(128))', ' $ColumnCast = ''$Search''' ) END;
IF (@CheckDateTime = 1) BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|datetime|time|smalldatetime|', 'FORMAT($ColumnName, '''+@castDateTime+''')', ' $ColumnCast = ''$Search''' ) END;
IF (@CheckXml = 1)      BEGIN INSERT INTO #Columncast ( [Typelist] , [Columncast] , [ColumnWhere] ) VALUES ( '|xml|', 'CAST($ColumnName AS NVARCHAR(MAX))', ' $ColumnCast LIKE ''%$Search%''' ) END;

BEGIN TRY DROP TABLE #Tables; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
CREATE TABLE #Tables ( TableId int IDENTITY(1,1) NOT NULL, TableName nvarchar(512) , ColumnName nvarchar(256) , ColumnWhere nvarchar(256) , ColumnCast nvarchar(512), SqlScript nvarchar(max) );

DECLARE @Template nvarchar(max) = 'SELECT $ReturnTop ''$TableName'' as TableName, ''$ColumnName'' as ColumnName, $Columncast as ColumnValue FROM $TableName with(nolock) WHERE $ColumnWhere';

DECLARE @CollateDatabase nvarchar(100);
-- Fix Collation bug
SELECT @CollateDatabase = collation_name  FROM sys.databases  where [name] = db_name();
--SELECT QUOTENAME(s.name) + '.' + QUOTENAME(o.Name) AS TableName , QUOTENAME(c.Name) AS ColumnName , p.ColumnCast , p.ColumnWhere FROM sys.columns c JOIN sys.tables o ON o.object_id = c.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.systypes AS t ON c.system_type_id = t.xtype JOIN (SELECT ColumnCast , [TypeList] , ColumnWhere FROM #ColumnCast ) AS p ON CHARINDEX('|'+t.name COLLATE SQL_Latin1_General_CP1_CI_AI +'|', p.[TypeList]) > 0;
DECLARE @TableTemplate nvarchar(max) = 'SELECT QUOTENAME(s.name) + ''.'' + QUOTENAME(o.Name) AS TableName , QUOTENAME(c.Name) AS ColumnName , p.ColumnCast , p.ColumnWhere FROM sys.columns c JOIN sys.tables o ON o.object_id = c.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.systypes AS t ON c.system_type_id = t.xtype JOIN (SELECT ColumnCast , [TypeList] , ColumnWhere FROM #ColumnCast ) AS p ON CHARINDEX(''|''+t.name COLLATE ' + @CollateDatabase + ' +''|'', p.[TypeList]) > 0'
PRINT @TableTemplate

INSERT INTO #Tables ( TableName, ColumnName, ColumnCast, ColumnWhere )
EXEC (@TableTemplate)

UPDATE #Tables SET SqlScript = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Template, '$TableName', TableName),'$ColumnWhere', ColumnWhere),'$Columncast',ColumnCast),'$ColumnName', ColumnName), '$Search', @Search),'$ReturnTop', @ReturnTop);
-- select * from #Tables;

BEGIN TRY DROP TABLE #Results; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
CREATE TABLE #Results (ResultId int IDENTITY(1,1) NOT NULL, TableName nvarchar(256) , ColumnName nvarchar(128) , ColumnValue nvarchar(MAX), SqlScript nvarchar(MAX) );
DECLARE @TableName nvarchar(256) , @ColumnName nvarchar(128) , @SqlScript nvarchar(MAX) , @TableId int,@SqlScriptCode NVARCHAR(max), @LastResultId INT;
WHILE (exists(select * from #Tables))
BEGIN
    SELECT @ColumnName = '', @TableName = '', @SqlScript = '';
    SELECT TOP 1 @TableId = TableId , @TableName = TableName , @ColumnName = ColumnName , @SqlScript = SqlScript FROM #Tables;
    DELETE FROM #Tables WHERE TableId = @TableId;
    SET @SqlScriptCode = REPLACE(@SqlScript, '''', '''''');
    SET @SqlScript = REPLACE(@sqlScript , '$SqlScriptCode', @SqlScriptCode);
    print @SqlScript

    INSERT INTO #Results (TableName, ColumnName, ColumnValue)
    EXEC (@SqlScript);

    SET @SqlScript = REPLACE (@SqlScript, ' FROM', ', * FROM');
    UPDATE #Results SET SqlScript = @SqlScript WHERE SqlScript IS NULL;

END
SELECT DISTINCT @@servername as [Servername], db_name() as DatabaseName, TableName, ColumnName, ColumnValue, SqlScript FROM #Results;
DROP TABLE #Results;
go

 

Source: (Old versions)  http://thesitedoctor.co.uk/blog/search-every-table-and-field-in-a-sql-server-database-updated/

Garbage : https://goo.gl/iDHkk

 

 

Get column creation datatype for declaration

Rédigé par M Sozezzo - - Aucun commentaire

This is quick way to get a string containing the sql datatype required for a column declaration, a create table, a print variables, or convert data to XML and XML to data, based on temp table and column information.

How to use:

1. Create your select statement
2. Define if you use or not prefix - you have prefix to column names and variable names.
3. Select and run the template to create your snippet code.


ex:

My select:
select top 100 * into #temp FROM [MY_TABLES_VIEWS]

Set prefix to variables : v_
Set prefix to columnes  : c_


Create my data with temp table


-- Delete temp table
BEGIN TRY drop table #temp; END TRY BEGIN CATCH END CATCH;
go
select * into #temp FROM (select top 100 * from myViewAndTable) as tb1
DECLARE @prefix_var nvarchar(50) = 'v_';
DECLARE @prefix_col nvarchar(50) = 'c_';

 

Lire la suite de Get column creation datatype for declaration

Fil RSS des articles de cette catégorie