Asked  6 Months ago    Answers:  5   Viewed   44 times

I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?

(SQL answers preferable over clicking about in the GUI of the management studio.)

 Answers

31

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified name, that table's columns are returned. If pktable_owner is not specified and the current user does not own a table with the specified pktable_name, the procedure looks for a table with the specified pktable_name owned by the database owner. If one exists, that table's columns are returned.

Tuesday, June 1, 2021
 
Hugo
answered 6 Months ago
34
UPDATE im
SET mf_item_number = gm.SKU --etc
FROM item_master im
JOIN group_master gm
    ON im.sku = gm.sku 
JOIN Manufacturer_Master mm
    ON gm.ManufacturerID = mm.ManufacturerID
WHERE im.mf_item_number like 'STA%' AND
      gm.manufacturerID = 34

To make it clear... The UPDATE clause can refer to an table alias specified in the FROM clause. So im in this case is valid

Generic example

UPDATE A
SET foo = B.bar
FROM TableA A
JOIN TableB B
    ON A.col1 = B.colx
WHERE ...
Tuesday, June 1, 2021
 
pamelus
answered 6 Months ago
82

You have to tell us exactly what connection string you are using, saying things like "I updated the connection to point to the database in the bin folder" is not helpful. Are you using a connection string like c:UsersMeVisual Studio 2010ProjectsMyProjectbindebugHKRMoviesDB.sdf? That is not right, that is hard-coded to your VS2010 debug test folder on your machine, and will not work for anyone else, will only work when you run from the debugger, and will not work when you deploy your app.

ADO.NET looks for vertical bars in connection strings and expands the enclosed string using the AppDomain property. For example, if you use Data Source=|DataDirectory|HKRMoviesDB.sdf from the VS debugger, DataDirectory expands to your project's bin folder, where VS copies the debug DLLs. If you deploy your app using an MSI installer, DataDirectory expands to your app's install folder chosen by the user. Further explanation is at this answer.

Be aware that DataDirectory may not be a directory writable by users; it is a good directory for read-only data, but if you want users to write to the file you should copy it to Environment.SpecialFolder.ApplicationData or somewhere in your app, as explained in this answer.

There is one more thing to keep in mind. When you are debugging you typically want to copy your original source data to the debug folder, test it, then discard your test data because you typically do not want your test data included in the final deployment. This is what the "Copy to Output Directory" property in your project does. If it is set to "copy if newer", which it probably should be, then any changes you make to your test data is temporary; the next time you update your original source data in your project, your test data will be discarded. Remember, your application's deployment directory is typically not writable by users so you should not be writing user data in DataDirectory anyway.

Tuesday, June 15, 2021
 
Crontab
answered 6 Months ago
61

If you specify type as an option to for xml, you can use an XPath query to convert the XML type back to a varchar. With an example table variable:

declare @MyTable table (id int, name varchar(50))

insert @MyTable (id, name) select 1, 'Joel & Jeff'
union all select 1, '<<BIN LADEN>>'
union all select 2, '&&BUSH&&'

One possible solution is:

select  b.txt.query('root').value('.', 'varchar(max)')
from    (
        select  distinct id
        from    @MyTable
        ) a
cross apply
        (
            select  CASE ROW_NUMBER() OVER(ORDER BY id) WHEN 1 THEN '' 
                        ELSE ', ' END + name
        from    @MyTable
        where   id = a.id
        order by 
                id
        for xml path(''), root('root'), type
        ) b(txt)

This will print:

Joel & Jeff, <<BIN LADEN>>
&&BUSH&&

Here's an alternative without XML conversions. It does have a recursive query, so performance mileage may vary. It's from Quassnoi's blog:

;WITH   with_stats(id, name, rn, cnt) AS
        (
        SELECT  id, name,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY name),
                COUNT(*) OVER (PARTITION BY id)
        FROM    @MyTable
        ),
        with_concat (id, name, gc, rn, cnt) AS
        (
        SELECT  id, name,
                CAST(name AS VARCHAR(MAX)), rn, cnt
        FROM    with_stats
        WHERE   rn = 1
        UNION ALL
        SELECT  with_stats.id, with_stats.name,
                CAST(with_concat.gc + ', ' + with_stats.name AS VARCHAR(MAX)),
                with_stats.rn, with_stats.cnt
        FROM    with_concat
        JOIN    with_stats
        ON      with_stats.id = with_concat.id
                AND with_stats.rn = with_concat.rn + 1
        )
SELECT  id, gc
FROM    with_concat
WHERE   rn = cnt
OPTION  (MAXRECURSION 0)
Tuesday, July 20, 2021
 
Kenny
answered 5 Months ago
56

use:

ALTER TABLE [dbo].[YOUR_TABLE]
ADD CONSTRAINT [MinLengthConstraint] CHECK (DATALENGTH([your_column]) > 2)

Reference:

  • DATALENGTH vs LEN
  • SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length
Friday, August 6, 2021
 
themihai
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share