2014-07-24 MSSQL database separation and get storage address

Our system is to support the installation of multiple sets on the same machine.

The installation method is the right mouse button "copy", "Paste" (the. mdf is automatically attached to the directory at startup).

Some people install multiple sets, want to delete a set of time, in order to save things on smart right mouse button "delete."

In this way, the database in MSSQL is chaotic, the customer will call the embarrassment.

We did not implement, two no customer service, on the old programmer and I two programmers ...

So I'm going to be a small tool to clean up the database and handle the database that is messed up by the customer:

First, find out all of our system's databases through the database name.

Then, iterate through each database and divide it into two types that can be connected and cannot be connected.

Finally, can be connected, data separation, can not be connected, directly deleted.

Detach Database T-sql:

1  Use  2ALTERDATABASE {0SETwithROLLBACK   3EXEC'{0}','true' ;  

Because this gadget has only a few fixed T-SQL, I write the global variable directly.

But I use the const? Or do you use the readonly?

This is a problem (although I've been a programmer for three years, but I've never seen them in my job, I'm ashamed of them).

So I Baidu a bit of the difference between the two:

Const must be assigned at the time of declaration, is a static constant, ReadOnly can be assigned in the constructor, is a dynamic constant.

The difference in usage, already known, that efficiency?

I don't know, but I guess static should be more memory-saving than dynamic.

Because "move" means not sure ah, so I use the const O (∩_∩) o~~~
After doing something good, the old programmer said there was a very troublesome problem.

If the client does not use this software for a long time, the "Compress content to save disk space" in the advanced properties of the. mdf file is checked.

Then you can't even get to the database ...

It seems that the database can not be connected, and can not be directly deleted, you have to get its storage address, and then determine whether the file exists.

exist to tell the customer the storage address of the. mdf file, and display the appropriate method of operation in an illustrated manner; the non-existent can be deleted directly.

However, how to get the storage address of the database? You have to ask me, ^_^ #~~~.

Get the database storage address T-sql:

1  UseMaster;2 SELECT 3Fs.[filename] 4  fromsys.databases asDB5 JOINSysaltfiles asFs onDb.[database_id]=Fs.[dbid] 6 WHEREDb.[name]='{0}';

In the beginning, I tried the smart one.

1 SELECT 2 [filename] 3  from  4WHERE[name]='{0}  '

After the result is executed, the returned result is zero embarrassment.

The original sysaltfiles storage is the physical address, sys.databases is the logical address.

Sad......

The main functions of the gadget have been realized (on the poor illustrations of the operating instructions), I am wondering what to take a name?

----the database grooming gadget.

Old programmer A look, no, called "Database one key repair", immediately feel on a level!

----What is Work experience? It's working experience! ~~~