Journey to SQL Authority with Pinal Dave

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 62 Comments

62 Responses

1. on June 21, 2007 at 5:00 am | Reply Shashi

   Excellenet Script

   
   
2. on July 2, 2007 at 2:12 am | Reply Danny

   Perfect

   
   
3. on July 3, 2007 at 11:12 pm | Reply RANGARAJAN

   REALLY HELPED ME.. GR8 SCRIPTING..

   
   
4. on July 9, 2007 at 9:16 pm | Reply anjali

   can there be multiple primary key (in multiple tables) in response to one foreign key in a table

   
   
5. on August 30, 2007 at 7:41 am | Reply Mark

   Hi
   Thanks for your query, it set me off in the right direction :).
   Much Appreciated.

   I have rewritten the query to be slightly faster:

   SELECT
   CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
   TABLE_CATALOG = FK.TABLE_CATALOG,
   TABLE_SCHEMA = FK.TABLE_SCHEMA,
   TABLE_NAME = FK.TABLE_NAME,
   COLUMN_NAME = FK_COLS.COLUMN_NAME,
   REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
   REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
   REFERENCED_TABLE_NAME = PK.TABLE_NAME,
   REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
   FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
   ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
   AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
   AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
   AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
   AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
   AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
   AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME

   Hope this helps :)
   -Mark

   
   
6. on August 30, 2007 at 7:44 am | Reply pinaldave

   Mark,

   Thanks for sharing the information. It is important to learn and share. I am glad that you follow the same understanding.

   Very good script.

   Kind Regards,
   Pinal

   
   
7. on September 3, 2007 at 7:33 am | Reply Edwin

   Hi Pinal,

   Am a beginner in SQLServer and I find your script to be very helpful.

   Do you have a similar script that lists all types of constraints, i.e. check constraints etc.?

   Any pointers to the right direction will be very much appreciated.

   
   
8. on September 5, 2007 at 11:20 pm | Reply Kanika

   Really fundoo, good that people like you believe in sharing information!

   
   
9. on September 6, 2007 at 4:01 am | Reply Subhash

   Hi Pinal,

   I used this script to find duplicate relationships. Thank you for this information sharing.

   
   
10. on September 16, 2007 at 7:31 am | Reply Prashant Pandey

    Hey, this is really a good querry, We can use when we want to know that how many primary keys and forign keys are there in the database.

    BUt as Subhash said that he used this querry to “find duplicate relationships” in the database.
    Wht is mean by “duplicate relationships” ???

    
    
11. on September 25, 2007 at 3:39 am | Reply kannan

    Hi,

    What is the query used to display all tables names in sql server (Query analyzer) ?

    Pls reply to this question? ASAP

    thanks and regards
    n.m.

    
    
12. on October 11, 2007 at 10:56 pm | Reply G2

    Excellent, Very impressed

    
    
13. on October 23, 2007 at 2:33 am | Reply Mark

    Hi there again

    I was just re-looking at this script, and was wondering about something:

    For the line in the script above that reads:
    WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’

    Is this necessary, because don’t all foreign key constraints correspond to Primary Keys? In the REFERENTIAL_CONSTRAINTS table, the FK columns correspond to UNIQUE_CONSTRAINT…. columns. Can a FK correspond to any unique constraint or only a Primary Key constraint?

    Thanks

    
    
14. on October 23, 2007 at 2:49 am | Reply Mark

    Sorry, just to clarify the above comment:
    The above mentioned where clause in your SQL is deftinately necessary because of the nested select statement.
    Removing the CONSTRAINT_TYPE clause in my script above will not make it any faster.
    I am just wondering what applies conceptually :)

    
    
15. on November 12, 2007 at 6:36 pm | Reply Tamer Mohammed

    hi every one
    Thank you alot i am a bigenier in developing software
    i want your advice to help me go in this field

    
    
16. on February 7, 2008 at 5:20 am | Reply Pat

    Nice! However, I don’t think it’s working properly on multi-column foreign keys.

    
    
17. on February 8, 2008 at 4:16 pm | Reply Santosh

    This is not a comment rather question.

    I went through the above post and found very interesting. I tried to modify it according to my problem but no luck.

    Problem.

    how to get the list of tables who has the FK relation ship with the given tables. once i get the list I want to delete the record from the parent table and set the references to null from the table where parents pk is referred as fk.

    I am not using cascade delete since I don’t want to delete the complete row.

    right now i am doing this by writing the simple SP and manually setting the null to the referenced tables.

    Thanks you very much in advance.

    -Santosh Maskar

    
    
18. on February 9, 2008 at 10:41 am | Reply Santosh Maskar

    after spending some time on the given SQL I solved my problem.

    Thanks a lot

    -Santosh

    
    
19. on March 20, 2008 at 5:26 pm | Reply Bala

    hi
    I need to delete the paticular records from parent table without affecting the child table(will have more child table) and also the relationship between the records should be removed,

    since am going to use the unaffected record in child table as parent table..
    Hope u got it..
    Your help is appreciated.

    Thanks & Regards
    Bala

    
    
20. on March 28, 2008 at 2:53 pm | Reply Johnson

    Thank for the script. It really help me.

    Thank

    Regards.
    Johnson

    
    
21. on April 5, 2008 at 5:11 am | Reply Richardz

    This was a great tutorial! It was no problem at all to adapt this and create a routine that automatically fixed that pesky name mangling the occurs in Sql Server constraints ( so that FK__Trader__departme__4B7734FF becomes FK__Trader__department_id).

    You helped me solve a real headache.

    
    
22. on April 22, 2008 at 1:15 pm | Reply Umer

    Really Nice Article… helped me alot :)

    
    
23. on April 24, 2008 at 7:42 pm | Reply merix

    This is good but if I have multiple columns in my foreign keys it will multiply the results so that 2 column returns 4 rows of data and 3->9 and so on.

    Adding the following line to Marks solution will reduce the amount of lines
    AND FK_COLS.ORDINAL_POSITION = PK_COLS.ORDINAL_POSITION

    Otherwise this was really helpful.

    
    
24. on May 27, 2008 at 1:09 pm | Reply Daniel Harrison

    Hi all,

    Great script, really helpful. I was wondering, is there a way i can change this script so that I can get a sensible order of integrity?

    Basically my problem is that we have this ’sync’ service that packages data up from remote sites and sends it to our central database. At the moment it just pipes all the data in an manual order that we set because we know about the constraints. What i would like is to be able to know which tables need to have their data first (ie tables that have their primary keys referenced as foreign keys in other tables).

    Is there a way to do that?

    
    
25. on June 12, 2008 at 4:31 pm | Reply Charanpreet Singh

    I needed a script which find all the table where primary key of a table is used in other tables as foreign key. i need to delete a record in master table ..
    This script worked well for me.
    thanks

    
    
26. on July 16, 2008 at 3:19 pm | Reply Charanpreet Singh

    suppose i have number of select statements in a single sp in sqlserver 2005.
    i need to know how many statements are there in that sp.

    
    
27. on August 14, 2008 at 11:11 am | Reply sameer

    hi…

    i want to delete rows from tables having “UserID” Coulmn in a databse named “Test” . but some tables are foreignkey constraints… how it can be done…

    first i have to find all tables having UserID column. then delete the rows from tables containg foreign key constraint(i.e where UserID=’SOMETHING’)…after that delete the tables having primary key..in a database…

    SUMMARY:: I HAVE TO CLEAR ALL THE ROWS FROM ALL THE TABLES HAVING USERID COLUMN IN A DATABASE WHERE SOME ARE FOREGNKEY CONSTRAINTS AND SOME ARE PRIMARY KEY…

    HOW IT’S POSSIBLE…

    
    
28. on August 26, 2008 at 7:42 pm | Reply Sandesh S

    Hey How about

    EXEC SP_Fkeys @TableName

    
    
29. on August 27, 2008 at 5:34 pm | Reply Pradeep

    Could we bind same default to multiple columns of the same table. If so then how.

    
    
30. on September 5, 2008 at 2:40 am | Reply Cowboy

    PINAL, I have a somewhat related problem that i was wondering if you had a solution for

    There are 4 tables with relationships to each other. ConferenceRoom, ConferenceRoomCapacity, TeleTrackPhone, and RPSLocation

    The table ConferenceRoom stores attributes pertaining to conference rooms.

    The table ConferenceRoomCapacity stores attributes pertaining to the maximum capacity of the conference room.

    The table TeleTrackPhone contains different phone numbers. It stores ConferenceRoomID and RPSLocationID as FKs.

    The table RPSLocation stoes info about different locations, city, state, etc.

    The TeleTrackPhone table allows duplicate values for the ConferenceRoomID because a conference room can have more than one phone number.

    This is the query that I run.

    SELECT DISTINCT A.ConferenceRoomID, A.ConferenceRoomName, C.City, C.State
    FROM ConferenceRoom As A, TeleTrackPhone As B, RPSLocation As C, ConferenceRoomCapacity As D
    WHERE A.ConferenceRoomID = B.ConferenceRoomID
    AND B.RPSLocationID = C.RPSLocationID
    AND A.ConferenceRoomID = D.ConferenceRoomID

    My problem is that it returns duplicates if the conference room has multiple phone numbers and i only want the query to return a list of conference rooms.

    
    
31. on October 3, 2008 at 8:14 pm | Reply Aniket Tamhane

    Nice one… It helped me to a lot during SQL Server Replication Setup

    
    
32. on October 4, 2008 at 6:17 am | Reply Imran Mohammed

    Hello,

    This one is simpler, just gives you table_name and which type of key information and key name.

    select OBJECT_NAME(PARENT_OBJ) TABLE_NAME, CASE WHEN XTYPE =’F’ THEN ‘FORIEGN KEY’ ELSE ‘PRIMARY KEY’ END KEY_TYPE , NAME KEY_NAME
    from sysobjects where Xtype in (‘F’ , ‘pK’) ORDER BY XTYPE DESC

    I am using only sysobjects table to get this information.

    Hope this helps.
    Imran.

    
    
33. on October 14, 2008 at 8:09 am | Reply Rich

    Fantastic script, thank you so much!

    
    
34. on October 28, 2008 at 10:54 pm | Reply Karim

    Hi,
    here’s another version (mine :-; ) to extract foreign keys.

    Replace ‘TABLE_NAME’ with yours … !

    Regards
    Karim Laurent

    select OBJECT_NAME(FKEYS.PARENT_OBJECT_ID) source ,PKCOLUMN_NAME= convert(sysname,col1.name), OBJECT_NAME(FKEYS.referenced_object_id) destination, FKCOLUMN_NAME = convert(sysname,COL2.name)

    from

    sys.columns COL1,

    sys.columns COL2,

    sys.foreign_keys FKEYS

    inner join sys.foreign_key_columns KEY_COLUMN on (KEY_COLUMN.constraint_object_id = FKEYS.object_id)

    where

    COL1.object_id = FKEYS.referenced_object_id

    AND COL2.object_id = FKEYS.parent_object_id

    AND COL1.column_id = KEY_COLUMN.referenced_column_id

    AND COL2.column_id = KEY_COLUMN.parent_column_id

    AND OBJECT_NAME(FKEYS.PARENT_OBJECT_ID)=’TABLE_NAME’

    
    
35. on November 17, 2008 at 1:13 pm | Reply Dalbir

    Thanks, Great scripting

    
    
36. on December 31, 2008 at 1:59 pm | Reply Smeet

    MAKE IT MORE SHORT N SIMPLE
    Will work on both 2000 and 2005
    ===========================

    select object_name(constid) FKey_Name, object_name(fkeyid) Child_Table, c1.name FKey_Col,
    object_name(rkeyid) Parent_Table, c2.name Ref_KeyCol
    from sysforeignkeys s
    inner join syscolumns c1
    on ( s.fkeyid = c1.id
    and s.fkey = c1.colid )
    inner join syscolumns c2
    on ( s.rkeyid = c2.id
    and s.rkey = c2.colid )

    
    
37. on March 8, 2009 at 3:41 pm | Reply Karan Kang

    Nice ..the script really helped me..!
    Thanks..!

    
    
38. on March 17, 2009 at 12:32 pm | Reply Sanjay

    I want to retrieve only limited data from database like limit is keyword in mysql which retrieve limited data according to our arguments. Like I want to load only 10 records from the database at first display in page. but I don’t want to using top. Because its create problem of paging. So please give me alternate solution for that’d have use row index but in that row index I have to write inner query and my data base I s to heavy.

    
    
39. on April 5, 2009 at 12:56 am | Reply Dinci

    Excellent script that got me going in the right direction. Thank you so much.

    
    
40. on April 22, 2009 at 7:12 pm | Reply Wannabag

    Excellent script thank you

    
    
41. on April 24, 2009 at 1:57 am | Reply Pablo Veliz

    excelente, gracias por publicarlo.

    
    
42. on April 27, 2009 at 3:50 pm | Reply Dakshina Murthy

    Hi,

    It was great, it helped me in finding the duplicate FK for the same set of tables for the same set of fields.

    With Regards
    Dakshina Murthy

    
    
43. on May 22, 2009 at 6:43 pm | Reply Jaseem

    Hi
    How to retrieve all columns of the given table and its foreign key table.
    Regards
    Jaseem, A.

    
    
44. on July 27, 2009 at 11:33 pm | Reply Ty A.

    This script is amazing, it saved my life.

    
    
45. on July 30, 2009 at 11:41 am | Reply Marie Magdz

    You guys rock! This has saved me sooo much time. Thanks all

    
    
46. on August 12, 2009 at 8:33 pm | Reply Kunal Shah

    Good script !!! saved lot time.

    
    
47. on September 11, 2009 at 12:28 am | Reply M

    Excellent site.

    The queries above (except for Smeet’s) return extra incorrect rows for compound FKs.

    I ran a SQL Profiler trace while scripting out a compound FK in Management Studio and here is what I found in the Profiler trace. The result additionally outputs an ID column that displays the correct order in which the PK table columns are referenced.

    Cheers

    select tbl.name as [Table_Name] ,
    cstr.name as [ForeignKey_Name],
    fk.constraint_column_id as [ID] ,
    cfk.name as [Name] ,
    crk.name as [ReferencedColumn]
    from sys.tables as tbl
    inner join sys.foreign_keys as cstr
    on cstr.parent_object_id=tbl.object_id
    inner join sys.foreign_key_columns as fk
    on fk.constraint_object_id=cstr.object_id
    inner join sys.columns as cfk
    on fk.parent_column_id = cfk.column_id
    and fk.parent_object_id = cfk.object_id
    inner join sys.columns as crk
    on fk.referenced_column_id = crk.column_id
    and fk.referenced_object_id = crk.object_id
    order by [Table_Name] asc ,
    [ForeignKey_Name] asc,
    [ID] asc

    
    
48. on October 1, 2009 at 11:12 am | Reply Rajnish Awasthi

    Very nice script, it is really helpful.

    
    
49. on October 19, 2009 at 8:15 pm | Reply Ty

    once again your SQL scripts have proven to be incredibly useful.
    Thank you!

    
    
50. on December 4, 2009 at 12:18 am | Reply usha

    Excellent script
    than kyou
    it hepls me a lot.
    I have question
    i have several(around 70) tables.
    Person table is the primary table.personId is the primary key in this table. eventhough primary key is unique I have several duplicate rows in the primary table(like all fields are the same except personID[primarykey]).
    I want to delete duplicates from the primary key table and same time I want to update that foreign key with the other in the foreign key table.
    could u help please help me regarding this.
    thank u

    
    
51. on December 4, 2009 at 2:01 pm | Reply Pinal Dave

    Hello Usha,

    There is no inbuilt functionality to perform the complete task straightway. You will have to write a batch to complete it. Here I can provide you a guideline to implement.
    Suppose the design of your Person table is as following:

    PersonID Name Age
    1 ABC 30
    2 XYZ 35
    3 ABC 30

    Here you want that in all foreigh key tables update the PersonID 3 (duplicate record) to 1 and then from Person table delete the record where PersonID is 3.

    At first Get the minimum PersonID for all duplicate records using following script:

    WITH CTE (PersonIDOld, PersonIDNew, DuplicateCount)
    AS
    (
    SELECT PersonID as PersonIDOld, MIN (PersonID) OVER(PARTITION BY Name, Age) AS PersonIDNew,
    ROW_NUMBER() OVER(PARTITION BY Name, Age ORDER BY Name, Age) AS DuplicateCount
    FROM Person
    )

    SELECT PersonIDOld, PersonIDNew INTO #temp
    FROM CTE
    WHERE DuplicateCount > 1

    Note: Here replace the column Name, Age with all columns of Person table except Primary Key column.

    Now update foreign key of PersonID in all foreign key tables as following:

    UPDATE FKTable1
    SET PersonID = PersonIDNew
    FROM FKTable1 INNER JOIN #temp tmp
    ON FKTable1.PersonID = tmp.PersonIDOld

    At last delete all duplicate records from Person table as following:

    DELETE Person
    FROM Person INNER JOIN #temp tmp
    ON Person.PersonID = tmp.PersonIDOld

    Please let me know if you need more clarification or if you have any issue.

    Kind Regards,
    Pinal Dave

    
    
52. on December 4, 2009 at 8:54 pm | Reply usha

    thank you so much sir
    I think it will workIi am beginner to sql
    I don’t know how to create and run batch
    could you help me reagrdng this

    SELECT PersonIDOld, PersonIDNew INTO #temp
    FROM CTE
    WHERE DuplicateCount > 1

    I executed until this as a query it shows 392 rows affected.

    I try to update the foreginkey query
    but it is not recognizing
    PersonIDNew,
    PersonIDOld
    could u help me regarding this.
    ————————————————
    and I have one more question
    can I update all the foreign key tables at a time and delete the duplicates from Primary key table.
    Can I run all this asa one batch
    —————————————————
    I have foreign key tables iin different databases also
    I am totally using 3 databases
    I need to update the foreign key’s from all those tables based on this primary key
    ————————————————————————
    and one condition i need to check
    in some tables this foreign key is also used as a primary key
    at that time it shows a error message like
    sequence contains more than one element
    when I try to oupdate that foreign key with new value
    —————————————————
    actually I wrote queries and try to access those through C#.net
    If u provide any direct solution through sql server or through C# programming is good for me.
    ———————————————–
    It is my first job I am working on this from last 3 weeks.
    If u provide any solution to this, I am really thank ful to you
    I appreciate it

    
    
53. on December 7, 2009 at 3:09 am | Reply usha

    could any one reply to my question,plese?
    i am relly waiting for the suggestions.

    
    
54. on December 8, 2009 at 8:40 pm | Reply usha

    I trie the CTE query
    it
    IT is working fine
    thank u som much
    but now I had another probem like
    when i try to update foreign key tables
    some tables are updating
    but some tables are shows a message like

    Msg 2627, Level 14, State 1, Procedure T_UPD_Membership_Question, Line 14
    Violation of PRIMARY KEY constraint ‘PK_Membership_Question’. Cannot insert duplicate key in object ‘dbo.Membership_Question’.
    The statement has been terminated.

    even I tried by removing relaton ships.still it won’t allow me

    please help me regrding this.

    
    
55. on December 9, 2009 at 4:10 am | Reply Pinal Dave

    Hello Usha,

    The error “Violation of PRIMARY KEY constraint ‘PK_Membership_Question’. Cannot insert duplicate key in object ‘dbo.Membership_Question’.
    ” occures when duplicate vaues are inserted in Primary key column.

    Make sure that foreign-key column is not primary-key or a part of primary-key in foreign-key table. If that is the case than you will have to drop the Primary-key constraint from foreign-key column in foreign-key table.

    Kind Regards,
    Pinal Dave

    
    
56. on December 9, 2009 at 9:43 pm | Reply usha

    thank u so much Mr. Dave
    I did try that
    for some tables it is working fine
    but for some tables it repetedly displays same message.
    is there any thing like even after i deleting the primary key and disabling triggers and unique indexes.

    can i check the entire row before updating
    if it isalready there then deleete the row and insert the new one which I wnat to update.
    or any other suggestions regarding this.
    thnk you

    
    
57. on December 15, 2009 at 5:19 am | Reply u_muhammad

    Sir i have a Problem statement given below plz canu help me of making query of this type of statement:

    Using the data dictionary of Oracle server, write down a single SQL statement to retrieve the parent and child relationship between all the tables of the currently connected schema.

    Sample output from HR schema:

    Employees ———hasParent———>Departments

    Departments ——-hasParent———>Locations

    Locations———hasParent———>Countries

    Countries———hasParent———>Regions

    
    
58. on January 11, 2010 at 2:21 pm | Reply jayant das

    Hi

    folloing query is running sucessfully second one is not running

    SELECT
    K_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
    INNER JOIN (
    SELECT i1.TABLE_NAME, i2.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
    WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
    ) PT ON PT.TABLE_NAME = PK.TABLE_NAME

    regards

    Jayant

    
    
59. on January 27, 2010 at 11:35 am | Reply Shanky

    Wow great it helped me a lot….
    Thankx

    
    
60. on February 5, 2010 at 9:13 pm | Reply Paul

    Fantastic Blog… I always find it very useful..

    I have a question , hopefully you can help. I’d like to include views in this script. I’m querying Microsofts CRM system and can only use views, it’s been heavily customised and looks pretty confusing.

    What I’d like to see is the view name and column name where the column within the view is a key column. For example If we have an Account table with a primary key of AccountID and there are another 5 tables with a foreign key relationship back to the account table. I’d like to see any views referencing these 5 tables that contain the foreign key column in the view defintion… Hope that’s clear. I’d be very grateful if you could offer some help..

    Regards

    Paul

    
    
61. on February 23, 2010 at 2:58 am | Reply Carlos Porras (El Salvador)

    Hi Mr. Pinal Dave,

    Is it possible to combine your query (the one shown above) with this one as to get a single file at once.

    What I’d like to do is to build the [(DisplayName ...... )] of the second part of the query with the results of the query you have provided here as to build a single file.

    select vrstica from
    ( SELECT t.name as tabela, as sort, ‘[MetadataType(typeof(' + t.name + 'Metadata))]‘
    + ‘ public partial class ‘ + substring(t.name, 1, len(t.name)-1) + ‘ {[DisplayName("' + t.name +'")]‘
    + ‘ public class ‘ + t.[name] + ‘Metadata{‘ as vrstica FROM sysobjects t WHERE t.xtype=’U’
    union all
    SELECT sysobjects.name, 2, ‘ [DisplayName("' + replace(syscolumns.name,' ','_') + '")]‘
    + case syscolumns.isnullable when 0 then ‘ [Required()]‘ else ” end
    + ‘ public object ‘ + replace(syscolumns.name,’ ‘,’_') + ‘{get;set;}’
    + ‘ //’ + systypes.name
    + ‘(‘ + ltrim(rtrim(str(isnull(syscolumns.prec,0)))) + ‘,’ + ltrim(rtrim(str(isnull(syscolumns.scale,0)))) + ‘)’
    + ‘ ‘ + ltrim(rtrim(str(syscolumns.length)))
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype = systypes.xtype and systypes.name not like ’sysname’
    WHERE sysobjects.xtype=’U’
    union all
    SELECT t.[name], 3, ‘}}’
    FROM sysobjects t
    WHERE t.xtype=’U’
    ) t
    ORDER BY tabela, sort

    Thanks in advance

    Carlos Porras (El Salvador)

    
    
62. on March 17, 2010 at 8:49 pm | Reply witecat

    Hi!
    Use Pinal’s beautiful script to list all PK and FK keys in a DB/table

    SELECT DISTINCT
    KeyColName = PT.COLUMN_NAME
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
    INNER JOIN (
    SELECT i1.TABLE_NAME, i2.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
    –Optional – to show only one/limited list of tables
    –WHERE i1.TABLE_NAME IN (‘object_registry’)
    ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
    ORDER BY PT.COLUMN_NAME

    BR
    /witecat