sql server, find foreign keys associated with given primary key

sql server logo

If you work with an unknown schema and try to investigate the relationships between primary and foreign keys, there is a great answer on stackoverflow originally posted by db2.


SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id

embedding ftp client in your android app

Πρωτόκολλα

Open your favorite IDE for android development and create a new project. Unless you choose to implement RFC 959 on your own, include a good ftp client library like this one http://commons.apache.org/proper/commons-net/download_net.cgi

If you already know how to include a jar in your android project, skip the following bullets.

  • For android studio fans: copy-paste jar to libs folder of your project, right click, “add as library”. If you don’t see the libs folder check this link @ stackoverfow.
  • For eclipse fans, see here.

Update your manifest.

<uses-permission android:name="android.permission.INTERNET"/>

Create an activity, add a button and program button’s click handler:

public class MyActivity extends Activity {
     protected void onCreate(Bundle b) {
         super.onCreate(b);

         setContentView(R.layout.content_layout_id);

         final Button button = (Button) findViewById(R.id.button_id);
         button.setOnClickListener(new View.OnClickListener() {
             public void onClick(View v) {
                FtpConnection c = new FtpConnection();
                c.password = "ftp_password";
                c.port = 21;
                c.server = "ftp.example.com";
                c.username = "ftp_username";

                FtpAction a = new FtpAction();
                a.upload = true;
                a.connection = c;
                a.remoteFilename = "remotefilename.bin";

                new FtpAsyncTask(MyActivity.this).execute(a);
             }
         });
     }
 }
}

Click handler fires an async task that handles the communication with ftp server. There are 3 important entities in the above snippet: FtpConnection, FtpAction, FtpAsyncTask. We will see all of them in the following paragraphs.

Android prevents us from running network operations on main thread. This is the reason why we need an AsyncTask to enable background ftp communication. Thus, UI remains responsive, and if we implement so, user can be informed about task’s progress.

FtpAsyncTask snippet:

import android.app.ProgressDialog;
import android.content.Context;
import android.os.AsyncTask;
import android.widget.Toast;

import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;

import java.io.ByteArrayInputStream;
import java.io.InputStream;

public class FtpAsyncTask extends AsyncTask<FtpAction, Void, Boolean> {

    private Exception exception;

    /** progress dialog to show user that the backup is processing. */
    private ProgressDialog dialog;
    /** application context. */
    private Context context;

    protected void onPreExecute() {
        this.dialog.setMessage("Starting");
        this.dialog.show();
    }

    @Override
    protected void onPostExecute(final Boolean success) {
        if (dialog.isShowing()) {
            dialog.dismiss();
        }

        if (success) {
            Toast.makeText(context, "OK", Toast.LENGTH_LONG).show();
        } else {
            Toast.makeText(context, "Error", Toast.LENGTH_LONG).show();
        }
    }

    public FtpAsyncTask(Context context) {
        this.context = context;
        dialog = new ProgressDialog(context);
    }

    protected Boolean doInBackground(FtpAction... actions) {
        FTPClient ftp = new FTPClient();
        FtpAction a = actions[0];
        try {
            ftp.connect(a.connection.server, a.connection.port);
            ftp.login(a.connection.username, a.connection.password);

            boolean success = false;

            byte[] test = new byte[]{0, 1, 0, 1, 0, 1, 0, 1};
            InputStream inputStream = new ByteArrayInputStream(test);
            ftp.setFileType(FTP.BINARY_FILE_TYPE);
            ftp.enterLocalPassiveMode();
            success = ftp.storeFile(a.remoteFilename, inputStream);
            inputStream.close();

            a.success = success;

            ftp.disconnect();

            return  success;
        } catch (Exception x) {
            this.exception = x;

            a.success = false;

            return false;
        }
    }
}

FtpAsyncTask extends AsyncTask by implementing doInBackground. This method uses ftp library to communicate with ftp server. In our example snippet, we create an 8-byte input stram and we upload it to ftp server with the name “test.bin”. Our snippet fires a progress dialog that informs user for the operation in progress. If something goes wrong an error message pops up (Toast).

FtpConnection and FtpAction snippet:

public class FtpConnection {
    public String server;
    public int port;
    public String username;
    public String password;
}

public class FtpAction {
    public FtpConnection connection;
    public String remoteFilename;
    public boolean success = false;
}