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:

SELECT 
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)

Kill all sql connections to a specific database

by Heathesh 18. March 2010 01:21

I wanted to figure out how (if possible) I could possibly kill all connections to a specific database and then take it offline. So what I did was create the following SQL script:

First I created a temporary table to use for storing the current connection info. The columns indicated in this table are the columns returned by the sp_who2 command.

CREATE TABLE #temp
(
    SPID1 int,
    [Status] varchar(500),
    [Login] varchar(500),
    HostName varchar(500),
    BlkBy varchar(500),
    DBName varchar(500),
    Command varchar(500),
    CPUTime int,
    DiskIO int,
    LastBatch varchar(500),
    ProgramName varchar(500),
    SPID2 int,
    REQURESTID int
)


Next I wanted to populate the #temp table I'd just created with the relevant connection data, so I ran:

INSERT INTO #temp
EXEC sp_who2


You can now select from the #temp table and you will see all the connection info at the time of the INSERT command above. So now to kill all the connections to a particular database, I created and ran the following script:

DECLARE
    @SPID int,
    @KillCmd varchar(150)

--change the My_Database_Name to your database name
DECLARE myCursor CURSOR FOR
SELECT SPID1 FROM #temp
WHERE DBName = 'My_Database_Name'

OPEN myCursor

FETCH NEXT FROM myCursor INTO @SPID

WHILE @@fetch_status <> -1
BEGIN
    SET @KillCmd = 'kill ' + CONVERT(varchar(50), @SPID)
    EXEC (@KillCmd)
    FETCH NEXT FROM myCursor INTO @SPID
END

CLOSE myCursor
DEALLOCATE myCursor


You will notice that I select the first SPID from the #temp table for my particular database. All you have to do is insert your particular database name in that portion of the script, run it and it will kill all the connections to that database. I have however notcied that some connections take a little time to die, not sure why this is, but I definitely found this script very useful.

Happy scripting!

Change the default path for database creation in SQL Server

by Heathesh 18. November 2009 20:58

Open SQL Server management studio, and right click on the instance name and select "Properties". In the properties window, select the "Database settings" page and you will see "Database default locations" on the window displayed. Change the folders, click "Ok".

It's that easy...

Tags: , , ,

SQL Server 2005 | SQL Server 2008

Converting a SQL text value to varchar in SSIS

by Heathesh 13. October 2009 20:10

When I first tried this I used a script conversion task, with a script that would Convert.ToString() the text value to the string value I wanted. However, the ToString was returning the type name, and not the converted string.

It seems the best way to do this is to use two Data Conversion tasks. First convert the value to a DT_TEXT field. Next create a Data Conversion and convert the DT_TEXT value to a DT_STR value.

This achieved the desired result.

Tags: , , , ,

SQL Server Integration Services (SSIS)



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

Certifications

Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions

 

Tag cloud

Calendar

<<  September 2017  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar

http://heathesh.com