Getting Foreign Keys both ways in SQL Server

I’m posting this one here for myself. Whenever I get lost finding foreign keys in a SQL Server database I’ll come back to this for a neat and tidy list of all foreign key constraints on a table.

This bit of SQL will go both ways for any given table. First it’ll get you a list of columns referencing other tables, then secondly a list of other tables referencing your table. Just supply it with a table name and you’re good to go.

DECLARE @TABLE_NAME VARCHAR(30) = 'Customer'

-- Foreign keys referencing other tables
SELECT	object_name(f.parent_object_id) ParentTable,
		col_name(fc.parent_object_id,fc.parent_column_id) ColumnName,
		object_name(f.referenced_object_id) RefTable,
		f.name ConstraintName
FROM	sys.foreign_keys f
JOIN	sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
WHERE	f.parent_object_id = object_id(@TABLE_NAME)


-- Foreign keys refrencing this as a parent table
SELECT	object_name(f.parent_object_id) ParentTable,
		col_name(fc.parent_object_id,fc.parent_column_id) ColumnName,
		object_name (f.referenced_object_id) RefTable,
		f.name ConstraintName
FROM	sys.foreign_keys AS f
JOIN	sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
JOIN	sys.tables t ON t.object_id = fc.referenced_object_id
WHERE	object_name (f.referenced_object_id) = @TABLE_NAME

I put this together based off of this StackOverflow answer.

Also check out the command sp_help 'TableName' for a bunch of useful information on a table, including foreign keys.

Multi-page PDF Printing for Kendo UI Grids

The examples on Telerik’s site are a little sparse for PDF printing. Sure, it’s easy to print a Kendo grid to PDF, but let’s say you want to include other sections of the web page, or add a header, without creating an entirely separate “printable view”.

It took a lot of experimenting to arrive at the simple solution below. What all does it do?

  • Prints any section of the HTML document, including grids
  • Automatically determines page breaks for large grids
  • Scales the PDF contents to look good on a standard piece of paper
  • Displays a header and footer on each page with page numbers

    All that power is packed into this JavaScript function:

        function page_grid_render_pdf() {
            kendo.drawing.drawDOM($("#printable_area"),
                {
                    paperSize: [1100, 1430], // Scaling in pt - 8.5"x11" page ratio
                    landscape: true,
                    margin: { left: "0mm", top: "15mm", right: "0mm", bottom: "10mm" },
                    template: $("#pdf-page-template").html()
                }).then(function (documentgroup) {
    
                    // Generate PDF file
                    kendo.drawing.pdf.saveAs(documentgroup, "KendoPDF.pdf");
                });
        }
    

    You’ll also need to use the following section as the header and footer template.

    <script type="x/kendo-template" id="pdf-page-template">
        <div class="pdf-page-template">
            <div class="pdf-page-header">
                <span style="font-size: 140%;">
                    PDF Printing with Kendo UI
                </span>
                <div style="float: right">Page #:pageNum# of #:totalPages#</div>
            </div>
            <div class="pdf-page-footer">
                <div style="float: right">Page #:pageNum# of #:totalPages#</div>
            </div>
        </div>
    </script>
    

    When you call the JavaScript printing function, it will render everything in <div id="printable_area"> My printable area contains user information and a grid with user data.

    The paper size is set very large to take advantage of your browser or PDF viewer’s built-in fit to page functionality when printing. When I set paper size to the standard “Letter” size, my Kendo grid was enormous, and only a few rows fit on each page. Using a large paper size prints it out as it appears on your screen.

    I set the side margins to zero, while leaving space for the header and footer, since again your browser or PDF viewer will add its own margins.

    You may notice I am not using any of the page-break options mentioned in the Kendo PDF documentation. I tried them all and this automatic version seems to work the best with large grids, and it doesn’t require any manual page breaks added to grid rows.

    Another tip is to include the Pako compression library that comes with Kendo UI: pako_deflate.min.js Kendo will automatically use it when included, and it reduced my PDF file size ten times over!