Category Archives: MSSQL

Get Date Entity

I’ve created an scalar function who returns the following date parts: year, month, day, quarter, week, hour, minute, second, day of year, day of week, date format (yyymmdd, yymmdd, ddmmyyyy, ddmmyy, ddmm, mmdd).

The example of usage is:

The result for this is something like this. Don’t forget to create also the function “FormatDate”  from the previous post. Enjoy and feel free to modify it as you need. 🙂

Posted in MSSQL.

Format Date

Next i show to you an scalar function who should return an text  with the date. The format of the date is according to the  @FormatMask  parameter.

The execution for this function should be something like this.

I’ve created an SP for testing this function.

Result of execution is the following.   Feel free to modify it as you need. 🙂

Posted in MSSQL.

Reorganize Indexes

This stored procedure reorganize the indexes for the DB_Name() – DB_ID() 🙂 I don’t remember from where i took some parts for this stored procedure. I’ve modify it according to my need in order to be used. I generally use this SP in a maintenance plan from RBCGxServerGeneral – RBC Etiqette

The procedure can be modified so that should reorganize indexes with an specific maximum fragementation percentage. Now this SP, reorganize indexes who are fragmented with more then 5 %. The execution of this SP should be as following.

The output message should be something like this. Enjoy it, and don’t forget to modify it as you need. …

Continue reading

Posted in MSSQL.

Database Truncate Log

This stored procedure “truncates” the log file for the DB_Name().

The execution of the stored procedure is made something like this:

The result output after the execution should be as following. The message result after the execution is the next. Feel free to modify it as you need. 🙂 Enjoy  

Posted in MSSQL.

Database Backup SP

Next I present you my implementation of an stored procedure for making backup to an database. This stored procedure works very good on SQL Server 2005 Express Edition.

This stored procedure can be used like this:

The return value should be 0 in case of success, or -1 in case of error. 🙂 The output messages should look something like this:   Enjoy and feel free to modify it as you need. 🙂

Posted in MSSQL.

Cross Table – Lines from Table1 are columns and Lines from Table2 are lines

When you need to create a table that imitates an cross tab in mssql – you can create a temporary table that can get the columns from an table, and the rows form another table. At the cross for this two tables, can be a bit type who can show if the relation between those two table has some value. First you need to see the  table schema. All the rows from table CodeTypes will be considered like table columns. All the rows from table CodeContents will be considered like table lines. The table CodeContentPerCodeType contains the cross values between this two tables. The mechanism for this stored procedure is …

Continue reading

Posted in MSSQL.