Finding constraints programmatically

Finding constraints programmatically

Post by Gary Grube » Sat, 16 Sep 2000 04:00:00



I am constructing several sql sp's to automate the reconfiguration of
several tables in a database. I can automate most of the structures and
properties of the files, but I don't know how to iterate through the
constraints that may exist on each table. I know it is possible to do this
using the system tables. Does anyone know how?

Gary

 
 
 

Finding constraints programmatically

Post by Umachandar Jayachandra » Sat, 16 Sep 2000 04:00:00


    Please look at the ODBC catalog SPs ( any SQL version ) or the SQL-92
INFORMATION_SCHEMA views in SQL70 / 2000.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Finding constraints programmatically

Post by Gary Grube » Sun, 17 Sep 2000 04:00:00


I do not know what you are referring to. Could you be a little more
explicit. Where are the ODBC sp's?

Gary


Quote:>     Please look at the ODBC catalog SPs ( any SQL version ) or the SQL-92
> INFORMATION_SCHEMA views in SQL70 / 2000.

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> ( Please reply only to newsgroup. )

 
 
 

Finding constraints programmatically

Post by BP Margoli » Sun, 17 Sep 2000 04:00:00


Gary,

If you using SQL Server 7.0, I'd suggest using the INFORMATION_SCHEMA views
... however if that option is not available to you ... the following is from
the SQL Server 7.0 Books Online, and might help ...

The SQL Server ODBC driver uses the following catalog stored procedures.

Stored procedure                 Returns
sp_catalogs                          List of all catalogs in a linked
server. If the linked
                                                server is a server running
SQL Server, the catalogs
                                                equate to databases.
sp_column_privileges       Information about column permissions for the
                                                specified table(s).
sp_columns                          Information about columns for the
specified table(s).
sp_databases                       List of databases.
sp_datatype_info                  Information about the supported data
types.
sp_fkeys                                 Information about logical foreign
keys.
sp_pkeys                                Information about primary keys.
sp_linkedservers                  List of all linked servers defined on the
local server.
sp_server_info                     List of attribute names and matching
values for the server.
sp_special_columns         Information for a single table about columns that
have
                                                special attributes.
sp_sproc_columns             Column information for a stored procedure.
sp_statistics                         List of indexes for a single table.
sp_stored_procedures       List of stored procedures.
sp_table_privileges             Information about table permissions for the
specified
                                                 table(s).
sp_tables                              List of objects that can be queried.

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated


> I do not know what you are referring to. Could you be a little more
> explicit. Where are the ODBC sp's?

> Gary



> >     Please look at the ODBC catalog SPs ( any SQL version ) or the
SQL-92
> > INFORMATION_SCHEMA views in SQL70 / 2000.

> > --
> > Umachandar Jayachandran
> > SQL Resources at http://www.umachandar.com/resources.htm
> > ( Please reply only to newsgroup. )

 
 
 

Finding constraints programmatically

Post by Erland Sommarsk » Mon, 18 Sep 2000 04:00:00


[posted and mailed]


>I am constructing several sql sp's to automate the reconfiguration of
>several tables in a database. I can automate most of the structures and
>properties of the files, but I don't know how to iterate through the
>constraints that may exist on each table. I know it is possible to do this
>using the system tables. Does anyone know how?

Here is some code I have lying around. They are taken from a Perl

Perl. Whether this code answers your question, I don't know, but it
might get you going.

This first bit renames a table and all it associated objects by prepending
"old_".

   SET NOCOUNT ON





   IF     EXISTS (SELECT * FROM sysobjects WHERE name = '$tbl') AND
      NOT EXISTS (SELECT * FROM sysobjects WHERE name = '$old_tbl')
   BEGIN
      DECLARE sql_rename_cur INSENSITIVE CURSOR FOR
         SELECT o2.name
         FROM   sysobjects o1, sysobjects o2, sysconstraints c
         WHERE  o1.name = '$tbl'
           AND  o1.id   = c.id
           AND  c.constid = o2.id
      OPEN sql_rename_cur

      WHILE 1 = 1
      BEGIN








      END

      DEALLOCATE sql_rename_cur
      EXEC sp_rename $tbl, $old_tbl
   END

And here is an entire Perl routine which generates SQL code when
run in its turn generates code that moves the foreign keys of
referencing tables, so that that they point to the new table.

   sub aba_tblfkey{

       my($pkey, $sql, $colvar_decl, $select_col, $syscolumns, $sysobj_join,




       # Sysreferences is not even in first normal form, so our SQL is
       # dependend of how many columns the primary key has. We create
       # a number of SQL element that we use in the SQL section below

       foreach $i (1..$#pkey + 1) {

          $select_col  .= ', c' . $i . '.name';
          $syscolumns  .= ', syscolumns c' . $i;
          $sysobj_join .= '   AND o2.id = c' . $i . '.id';
          $syscol_join .= '   AND c' . $i . '.colid = r.fkey' . $i;


       }

       $sql = <<SQLEND;
   -- Move foreign keys from $old_table to $this_table.
   SET NOCOUNT ON



           $colvar_decl





   DECLARE sql_fkey_cur INSENSITIVE CURSOR FOR
      SELECT o2.name, o3.name $select_col
      FROM   sysreferences r, sysobjects o1, sysobjects o2, sysobjects o3
             $syscolumns
      WHERE  o1.name  = '$old_table'
        AND  o1.id    = r.rkeyid
        AND  o2.id    = r.fkeyid
        AND  o3.id    = r.constid
        AND  o2.name  <> '$old_table'
        $sysobj_join
        $syscol_join
   OPEN sql_fkey_cur


   WHILE 1 = 1
   BEGIN


         BREAK

      -- First create the new reference under a new na,e

24)


+
                   " FOREIGN KEY (" + $ref_col + ") REFERENCES $this_table
($pkey)"


      -- Now drop the old infomation. We could do this at first, because
      -- then we would have lost information in case of failire.



      -- Rename the new reference to the real name.


   END

   DEALLOCATE sql_fkey_cur
   SQLEND

--

 
 
 

1. Finding Default Data Directory Programmatically

I've seen this topic kicked about a bit in my Google searches, but it was
either answered incorrectly or not quite what I need.

THE PROBLEM:  I need to be able to create a database programmatically (via
OSQL) in SQL 2000 from a remote system.  In the CREATE DATABASE statement, I
need to specify paths for the .mdf and .ldf files.  I want them to be put in
the default data directory.

NON-SOLUTION #1:  Omit any path information in CREATE DATABASE, e.g.:
---------------------
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'GF1')
DROP DATABASE [GF1]
GO
CREATE DATABASE [GF1] ON (NAME = N'GF1_dat', FILENAME = N'GF1.mdf' ,
    SIZE = 5, FILEGROWTH = 10%) LOG ON (NAME = N'GF1_log',
    FILENAME = N'GF1.ldf' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
---------------------
IMO, this should do exactly what I want--I don't specify a path so, It goes
into the default path!  Unfortunately, this returns an error:
---------------------
Device activation error. The physical file name 'GF1.mdf' may be
incorrect.
Msg 1802, Level 16, State 1, Server C3P0, Line 2
CREATE DATABASE failed. Some file names listed could not be created.
Check previous errors.
-------------------------
NON-SOLUTION #2:  Read the path from the registry, e.g.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot, e.g.
via xp_regread or some such.  Unfortunately this does not work if the
server's default data path has been changed post-install.
-------------------------
NON-SOLUTION #3:  Find the pathname for the 'master' DB and use that--but
that suffers from the same limit as #2.
-------------------------
It's hard to comprehend why this is so tough to do--either I'm missing
something or SQL2K is!  Thanks for any ideas.

Paul J. Neary
G&Z Systems, Inc
Hawthorne, NY
paul AT g-and-z DOT com
(please reply to NG)

2. Open an Access Report from VB

3. Finding SQL Server /servers/ and databases programmatically...

4. SQL2K SP 1 install error

5. Programmatically finding SQL Servers

6. Locale-dependent case conversion in {identifier}

7. Programmatically find SQL Version (7.0)

8. Help needed on Automating Export / Import

9. Finding Free Space via SQL (ie, programmatically)??

10. How to programmatically find the MSDE server name

11. Finding Primary Key programmatically

12. Finding constraints in existing database

13. Find names of all constraints on column