Traceonly option is currently not supported

Problem :

set autotrace traceonly;

Execute above query on sql developer.

If you are looking for traceonly option in sqldeveloper then answer is no , they query you executed on oracle CMD will not work on SQL Developer.

Instead you can write the query on SQL Developer whose xplain plan your trying to get.

It is more simple in SQL Developer , see below how

1. Write a SQL query on SQL Developer
2. Select query
3. Click on third icon (right to "Execute statement") "Explain Plan) or Press F10
4. On Pressing third icon click on , it will generate the query and 
5. Execute it

Deleted Offline Database? No Issue

Yes you read right, no worry if you deleted Offline database it is easy to recover.

Very first thing all must aware that if you delete any Offline database, SQL Server will not delete mdf and ldf files from filesystem. Still there are chances of recover database directly using old mdf files you can create new database.

Benefits :

• Easy to Restore
• Easy to Recover
• No loss of tables and data

Use below query to create new database.

Here is mdf file of offline database which is still exists on filesystem after deleting of offline database

CREATE DATABASE offlinedbname ON 

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\offlinedbname.mdf' )

FOR ATTACH

GO

Delete Offline Database and Space Recovery

This is very recent post i have read SQL Server will not delete mdf and ldf files if database is Offline state.

This Problem has been observed in case of recovering database and action performed to delete database which are Offline. So after deleting off line database one have to manually delete the mdf and ldf file from filesystem in order to recover the space.

Is it safe to delete orphan mdf , ldf filesfrom file system if no database exists.

Answer is Yes it is safe to delete files.

SQL Server Audit Table Total Count Total Nulls Len of Data

Hello engineers, are you guys working on SQL Server. Are you guys working on TSQL. If yes i have written a TSQL program which right example of

• Execute dynamic SQL
• In Parameters/variables
• Out parameters/variables
• IN/Out in dynamic SQL
• Cursor in TSQL
• Audit of all tables data
• How many records in table
• How many distinct records in each field
• How many not null records
• Maximum length of data in each field

create table all_counts (id int identity , table_name  nvarchar(200), column_name  nvarchar(200), data_type nvarchar(200) , total_count int, distinct_count int , notnull_count  int, data_len int)

Declare 

@table_name varchar(50),

@column_name nvarchar(100),

@data_Type nvarchar(100),

@total_count integer,

@distinct_count integer,

@notnull_count integer,

@data_len integer,

@query_str nvarchar(4000),

@query_str2 nvarchar(4000) ,

@query_str3 nvarchar(4000) ,

@query_str4 nvarchar(4000) ;

declare cur_col Cursor for 

select  

t.TABLE_NAME

,column_name

,data_type 

from 

INFORMATION_SCHEMA.COLUMNS c 

join INFORMATION_SCHEMA.tables t on c.TABLE_NAME = t.TABLE_NAME 

where 

t.TABLE_TYPE ='BASE TABLE' 

open cur_col

fetch next from cur_col into @table_name,@column_name,@data_Type

while @@FETCH_STATUS=0

begin

Set @query_str='Select @total_count= count(@column_name1) from '+ @table_name;

exec sp_executesql @query_str, N'@column_name1 nvarchar(500),@total_count int output' ,@column_name1=@column_name,@total_count=@total_count output

Set @query_str2='Select  @distinct_count= count( distinct ' + @column_name + ') from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str2, N'@distinct_count int output' ,@distinct_count=@distinct_count output

Set @query_str3='Select  @notnull_count= count(  ' + @column_name + ') from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str3, N'@notnull_count int output' ,@notnull_count=@notnull_count output

Set @query_str4='Select  @data_len= len(max(  ' + @column_name + ')) from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str4, N'@data_len int output' ,@data_len=@data_len output

insert into all_counts select @table_name, @column_name, @data_Type, @total_count, @distinct_count, @notnull_count, @data_len

fetch next from cur_col into @table_name,@column_name,@data_Type 

end

close cur_col

deallocate cur_col

I hope this program is helping many engineers in different ways. Rest of usage is depend upon the thinking and capability of an engineer. He/she can modify this program to any extent since this is generic TSQL.

If any faced error while executing above TSQL cause of Table Name or Column Name have special character which was not supported by Dynamic SQL.

Error :

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'tablename'

Solution:

We need to update table_name and column name variables from Cursor as below

['+ @table_name +']';