Get list of all foreign key table and column names in SQL Server

by Heathesh 5. December 2014 02:04

I came across a problem whereby I wanted to retrieve the table and column names of all the foreign keys in SQL Server. The script I ended up using was fairly straight forward, but I thought it might be useful to someone else so I thought I should share it:

OBJECT_SCHEMA_NAME(fk.parent_object_id) ParentTableSchema, 
OBJECT_NAME(fk.parent_object_id) ParentTableName,
COL_NAME(fk.parent_object_id, fkc.parent_column_id) ParentTableColumn, 
OBJECT_SCHEMA_NAME(fk.referenced_object_id) ReferencedTableSchema, 
OBJECT_NAME(fk.referenced_object_id) ReferencedTableName,
COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) ReferencedTableColumn
FROM SYS.foreign_keys FK
JOIN SYS.foreign_key_columns FKC ON FK.object_id = FKC.constraint_object_id

Happy scripting!

Tags: , ,

Sql Server Reporting Services (SSRS)

Calling or using a web service method with parameters as a data source for a report

by Heathesh 21. July 2010 03:11

Using Visual Studio 2008, I came across an interesting problem a couple of developers were having trying to call a web service method with parameters and use it as a data source for a report. I actually found it's quite a finicky thing to do, but is relatively easy.

To begin with, I created a web service with a single method as a sample. My web service contained a method called GetPerson which expected a int parameter called id which would return a person object for the specified id.

Next I added a new report to a Visual Studio 2008 reports project I had already setup. Using the Report Wizard I specified the datasource type as XML, and added the URL of the web service as my connection string. You might have to click on "Credentials" and select "Use windows authententication" if you have a problem moving to the next window.

You should be presented with the "Design the Query" window next. Here click on the "Query Builder" button. Copy and paste the following text into your Query Designer:

   <Method Name="GetPerson" Namespace="">
       <Parameter Name="id">
   <ElementPath IgnoreNamespaces="true">*</ElementPath>

Things you have to change.

To start off with change the <SoapAction>. Access the web service using your browser e.g. go to "http://localhost/ReportWebService/DataService.asmx" and click on the method you're trying to run. In the description of the service you should see the name of the SoapAction you should use:

SOAP 1.1
The following is a sample SOAP 1.1 request and response. The placeholders shown need to be replaced with actual values.

POST /ReportWebService/DataService.asmx HTTP/1.1
Host: localhost
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: ""

Use the above mentioned SoapAction as specified above in the query:


Next you need to specify the method name and namespace. The method name should be simple enough, but be careful to include any trailing slashes "/" in the namespace should they be required (take note of the trailing slash at the end of my namespace):

   <Method Name="GetPerson" Namespace="">

Next change the Parameter info as required. Please remember Parameter names are case sensitive and should be specified exactly the same as in your WSDL of your web service. Also I recommend always specifying a default value that will return data, this makes it easier for you to check that your method is executing properly from within your query designer (click on the exclamation mark "!" to run the query).

       <Parameter Name="id">

The rest of the query should be left as is. Now that you've got your query setup, you can continue through the wizard setting up the other features of your report until your report has been created.

Adding the report parameters

The next step is to create report parameters that map to your web service method parameters. To do this open the "Report Data" window on the left. This window should appear next to your "Toolbox" and "Server Explorer" windows on the left of your Visual Studio IDE. Once the window is open, right click Parameters and select "Add new parameter". Give the parameter a relevant name etc. and set the data type to the relevant data type of the parameter of your web service and click "Okay".

Next right click the DataSet you created in the "Report Data" window (which is the web service dataset) and select "Dataset Properties". Click on "Parameters" in the window that opens and then "Add". Set the parameter name to the name of the parameter on the webservice method, for example my method needs a parameter called "id" so that's the name I used and in the drop down for the parameter value select the report parameter you created above then click "Okay".

Alright, that should be it. Select preview to view your report and you should be able to enter a parameter value and select "View Report" to view your report...

Happy Reporting!


Powered by BlogEngine.NET (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)


Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions


Tag cloud


<<  May 2017  >>

View posts in large calendar