Export MS SQL Database schema with C#

A few days ago, I was searching for a way to export MS SQL schema of all databases of a server for backup and migration purposes. Although I found some tools, what I needed was a way to get stored procedures, functions, views, triggers and everything even if it was encrypted.

Since I couldn’t find a way to automate this (e.g. run it every night to backup schema of my server), I thought it would be a nice weekend project.

You can read more about it, or download project from https://github.com/georgekosmidis/mssql-schema-dump!

Continue reading

Advertisements

SQL Server: Using RANK to display PERCENT (percentage of relevance)

So you end up with a query like this:

SELECT *
FROM table AS FT_TBL INNER JOIN
   CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
   ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 0
ORDER BY KEY_TBL.RANK DESC

That yields something like this:

ID ... KEY RANK
5  ...  5  765
2  ...  2  322
3  ...  5  128
1  ...  1   75

And you are missing a percentage column. Well, not any more!
Continue reading

SQL Server: How to simulate DROP TRIGGER IF EXISTS

I usually store triggers, store procedures, functions, queries etc in sql files in an SVN repository. I then use an app I wrote, that opens each file and executes it. The problem of course is CREATE and ALTER. I can’t use CREATE because the same trigger might already be there. On the other hand I couldn’t use ALTER because some triggers might be new.

Since there is no DROP TRIGGER IF EXISTS, I came up with this:

Continue reading

SQL Server: Using .NET assemblies (CLR)

SQLCLR is a technology for hosting of the .NET CLR engine inside SQL Server. Adding an assembly can be a lifesaver sometimes, since the limitations of coding in TSQL stored procedures, triggers or functions are a lot and profound.

Just follow these steps:
1. Create a new class library using Studio
New Class Library Project

Continue reading

SQL Server: Weird behavior with CONTAINSTABLE, ISABOUT and weighted terms

I came across the following weird behavior in an ISABOUT query in SQL SERVER, which sums up to the following bullets. Further down you can read the exact queries that reproduce this behavior and a not so elegant solution.
Best match for word “weird” is result with KEY 1:

  • weight(1): RANK of KEY 1 is 249
    (results order 1,2,3)
  • weight(0.8): RANK of KEY 1 is 321
    (weight down => rank up, results order 1,2,3)
  • weight(0.2): RANK of KEY 1 is 998
    (weight down => rank up, results order 1,2,3)
  • weight(0.17): RANK of KEY 1 is 802
    (weight down => rank down, results order 2,3,1)
  • weight(0.16): RANK of KEY 1 is 935
    (weight down => rank up, results order 2,1,3)
  • weight(0.01): RANK of KEY 1 is 50
    (weight down => rank down, results order 3,2,1)

As you can see, from 0.2 to 0.17 ranking decreases and results are messed up! From 0.16 results are inverted (the weight values that reproduce this behavior depend on terms, columns searched, etc). It seems there is a point where weight means the opposite, something like “do not include this term”.

Microsoft states here that the actual value of RANK is meaningless. But what about results order!

Continue reading