Category: SQL Server

One good way is to build a dynamic SQL statement, and then run it with sp_executesql. The syntax of the sp_executesql statement is as follow: (See detail documentation of the statement on Microsoft Developer Network site)

sp_executesql [@stmt=] statement
[
  {, [@params=] N'@parameter_name data_type [OUT|OUTPUT][,...n]'} 
     {, [@param1=]'value1'[,...n]}
]

1. Declare a unicode variable for the dynamic SQL statement:

   DECLARE @sSQL nvarchar(max) = ''

2. Build your dynamic SQL statment.
   1. For example, if you wish to replace all empty character fields in table [myBlog] then the statement would be:

      SELECT
      @sSQL = @sSQL + 'UPDATE [' + TABLE_NAME + '] SET [' +
          COLUMN_NAME + '] = NULL WHERE ([' + COLUMN_NAME +
          '] = '''')' + CHAR(13)
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (TABLE_NAME='myBlog') AND (DATA_TYPE LIKE '%char%')
      

   2. Or, if you wish to replace all of them in all the tables of the current database:

      SELECT
      @sSQL = @sSQL + 'UPDATE [' + TABLE_NAME + '] SET [' +
          COLUMN_NAME + '] = NULL WHERE ([' + COLUMN_NAME +
          '] = '''')' + CHAR(13)
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (DATA_TYPE LIKE '%char%')
      

3. Execute the dynamic SQL statement.

   EXEC sp_executesql @sSQL
   

Reading the SQL Server error logs.

sp_readerrorlog

sp_readerrorlog is a stored procedure in the master database. It checks that the user is part of the securityadmin, then calls xp_readerrorlog. Header of the log gives server information and location of the log files.

Parameters

Order	Type	Description
1	int	number of the log file. default is 0 for current file
2	int	LogType, default 1. 1 = SQLServer logs, 2 = SQLAgent logs, 3 = full text logs
3	varchar(255)	default NULL — search string for the log entry
4	varchar(255)	default NULL — search string for the log entry
5	datetime	from date-time
6	datetime	to date-time
7	varchar	Order, default to “ASC”. “ASC” or “DESC”

Start a new error log

sp_cycle_errorlog

By default the number of logs is 7.