I have captured some trace output for performance evaluation for an application which has just been upgraded. Originally, this application can only run with database compatible 70.
So, after we have switched this level from 70 to 80, I noticed that all T-SQL statements which executed thru the use of "sp_executesql" have much higher IO usuage. The usage increased from approximately 50 I/O to approximately 12000 I/O. When I reviewed the profiler output, I noticed that all select statements which executed thru this "sp_executesql" statements performed "index scan".
When I switch back to run with database compatibility level 70, my profiler output shows that all these "sp_executesql" statements performed "index seek".
All these statements use the same unique non-clustered index.
Is it a SQL Server bug? Does anyone know which service pack or hot fix address this problem?
In a new environment where we have 2005 servers and loads of databases that are running in compatibility mode with 2000 (80)
Are there any queries we can run on that can tell me what compatibility level each db is?
Looked on the web for ages apart from here but only info for changing the compatibility level - sp_dbcmptlevel – but queies only seem to be for changing it not and not reporting on it.
But I obviously do not want to do that for a production environment.
I want to do a mini report as to what db’s are still (I think all) 2000 mode and then suggest upgrades – I can go into the properties of each one but I know there must be an easier way – finding a load of admin queries that require cross apply and for that I need 2005 (90) compatibility….or is that only for the master db?
We have been using SQL 2005 in our dev enviroment for a few weeks, and I have just completed migrating our production server to a 64bit instance of SQL 2005 standard.
I have 3 publications, 1 snapshot, and 2 transactional. All go to SQL 2000 databases.
Our dev enviroment is in compatibility level 90, and our application is working fine. My concern is, will changing the production compatibility level from 80 to 90 affect / break replication to SQL 2000 Servers?
Hi,I need to move a database from an instance running SQL Server 2000 SP3to another running SQL Server 2000 SP2. Can I just use backup/restoreor detach/reattach and let SQL server take care of any downgrading (ifany).Many thanksGiovanni
I have a database in which I have some tables in which I have implemented Clustered columnstore Index. How to find the fragmentation levels of all these indexes via a single T-SQl script
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
Hi! After upgrading SQL Server from 6.5 to 7.0 my production database compatibility level is "65". I checked that by executing sp_dbcmptlevel <database_name>. I can change it to "70" but my question is how it's going to affect the application and do I have to change it?
I just upgraded to SQl Server 2000 from 7, and realized that all my reports created with existing scripts no longer work fine. The reports appear corrupt. Below is an example of a report script:
declare @vSubject varchar(30), @vMessage varchar(20) Begin
I have been migrating Databases from a SQL Server 7.0 Instance to a 2000 Instance. Basically the method I use is as follows:
a)Create a new Database on the Destination Server (same name as the Source) When I create the new Database on my destination server, the compatibility mode is '80' and the source is always '70'
b) do a 'revlogin' on the Source Server and use the output from that query to recreate the logins on the destination server
c) make sure that the default DB for the newly creates logins are correct and change them if necessary
d) Backup the DB on the Source Server and Restore it to the Destination Server.
e) Check login permissions and fix any orphaned users - usually I don't find any that need to be fixed, but I always check.
I've read BOL on Changing the compatibility mode on the Database... but I'm still unsure when I would have to do this and why???????????????? SHOULD I be changing the compatiblity mode from 80 to 70 when migrating Databases from 7.0 to 2000???? Any advice on moving DBs in this manner would be appreciated.
When my app starts up I want to ensure that the database compatibility level has been set to 90. I know about sp_dbcmptlevel, but that only seems to work in an interactive session; the documentation says it can't be used in a stored procedure, and that appears to be true. Does anyone know how I could read the database compatibility level in a stored proc?
In topic http://forums.microsoft.com/TechNet/showpost.aspx?postid=3143663&siteid=17 I have mentioned a problem I have. I was told that this forum is a better place to ask for a solution.
The problem I have:
On a fresh installed Windows 2003 R2 with SP2 (x86) I have installed ACT 5.0.5428.1056. The installation went well but no data is transferred from the ACT Share to the database. All the data is moved to the 'Failed' folder. On the same server MS SQL Server 2005 with SP2 has been installed successfully.
In the eventvwr I see the following error messages:
Source: ACTUpload Categorie: NONE EventID: 0
ACTUPLOAD: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms.
at System.Security.Cryptography.MD5CryptoServiceProvider..ctor()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeConstructor(Object[] args, SignatureStruct& signature, IntPtr declaringType)
at System.RuntimeMethodHandle.InvokeConstructor(Object[] args, SignatureStruct signature, RuntimeTypeHandle declaringType)
at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Security.Cryptography.CryptoConfig.CreateFromName(String name, Object[] args)
at System.Security.Cryptography.MD5.Create(String algName)
at System.Security.Cryptography.MD5.Create()
at Microsoft.ApplicationExperience.Common.DataManipulation.HashGenHelper..ctor(ObjectContext existingContext)
at Microsoft.ApplicationExperience.Common.DataManipulation.XmlDataProcessor..ctor(String inputConnection, EventLog actLog, XmlSchemaCollection schemaCollection, ObjectContext objectContext)
at Microsoft.ApplicationExperience.Common.DataManipulation.XmlDataService.InsertXmlDocument(String xmlFile, Boolean checkVersion)
at Microsoft.ApplicationExperience.Common.DataManipulation.XmlDataService.InsertXmlDocument(String xmlFile)
[queuer.exe] [Queuer] Cannot insert log file \SPNLAPP90125ACT_LogFilesSTATUS-WPNLL9000812{8C869987-5D71-4DD2-9893-7405B274E13C}.xml to the database! Details:
The Act Log Processing Service is running with the logon as a service account. This account is local admin on the ACT-server. Also this account has 'db-owner', db-datareader' and 'db-datawriter' rights on the ACT-database.
Other installed software: - Dot Net 1.1 - Dot Net 2.0 MS SQL Server 2005 Details: MS SQL Server 2005: Microsoft SQL Server Management Studio 9.00.3042.00 Microsoft Analysis Services Client Tools 2005.090.3042.00 Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 7.0.5730.13 Microsoft .NET Framework 2.0.50727.832 Operating System 5.2.3790
---> My question is: what do I have to do to get this running?
We have a reasonably large (several TB) database that was recently migrated from 2008 to a new box running 2014. Before giving it back to the users we forgot to change the compatibility level of the DB to bring it up to date with it's new environment.
We want to do some testing with backup compression so want to change the compatibility level, but we are unsure whether making the change on such a large database would cause slowness or downtime for our users.
Does the process of changing the compatibility level simply allow options that are not available in the older version or does it make structural changes to the database that would cause the users to notice slowness or downtime?
We are planning to upgrage our SS 2000 databases(couple of hundreds) to SS 2005.
To minimise the potential work arounds, it is agreed to have complex databases, continue running on compatibility mode 80. What are the potential impact of having database on mode 80(apart from not being able to use new feature)on SS2005?
Fruther how we can we import SS 2000 DTSs to SS 2005 SSIS in an cluster environment?
I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.
I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?
Hello all,i'm moving all my development to the new 2005/Fw2 environment.Now, i have an MSSQL 2005 server on the development server. To restore an existing database, i've created a new database with MSSQL 2000 compatibility level, and i've restored the backup over it. Everything seems ok, but when i try to create a diagram, i get the following error message:"Database diagram support objects cannot be installed because the database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."I've checked the properties and there seems to be the right owner (sa). If i create a new database with 2005 compatibility level, everithing works fine.Where is the problem?Also, a side question: by setting compatibility level to 2000, is it correct that the database can be deployed to a 2000 Server environment with no problems?Thanks in advance for any advice. -LV
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
Hi, pls can anyone help me out with this? I'm installing SQL2005 SP2 over an Itanium machine with W2003 SP2 but it keeps on failing on those features. Client components got installed with no problems. I've installed SQL2005-SP2 over x86 and x64 (not Itanium) machines many times before successfuly. I've been investigating this issue for several days with no luck so I'm a bit clueless at the moment. As I'm using Administrator account I think it is not file permission related. Summary says "Unable to install Windows Installer MSP file".
I have just started working the 2047 OLAP and came arcross the Analysis Service Limits. It states that the levels in a cube has a limit of 256 and the leves per dimension is 64. I am confused.
What is the definition of (levels in a cube)! and how are the levels in a cube different from (levels per dimension)
I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.
In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.
I'm using the Bush family in this example, these are the relationships: - George SR --- George JR ------ Jenna ------ Barbara --- Jeb ------ Jeb JR ------ Noelle
These statements will create and populate the tables for the example with the above relationships:
SET NOCOUNT ON DROP TABLE KK_Kids, DD_Dads, GG_Grandpas CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20)) CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20)) CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))
INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR') INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR') INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB') INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' ) INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' ) INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' ) INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )
So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads. I am getting this, which is not what I want:
- George SR --- George JR --- Jeb ------ Jenna ------ Barbara ------ Jeb JR ------ Noelle
SELECT 1 as Tag, NULL as Parent, GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName], GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id], NULL as [DD_Dads!2!DD_DadName], NULL as [DD_Dads!2!DD_Dad_Key!id], NULL as [DD_Dads!2!DD_Grandpa_Key!idref], NULL as [KK_Kids!3!KK_KidName], NULL as [KK_Kids!3!KK_Dad_Key!idref] FROM GG_Grandpas UNION ALL SELECT 2 , 1 , NULL , GG_Grandpa_Key , DD_DadName , DD_Dad_Key , DD_Grandpa_Key , NULL , NULL FROM GG_Grandpas, DD_Dads WHERE GG_Grandpa_Key = DD_Grandpa_Key UNION ALL SELECT 3 , 2 , NULL , GG_Grandpa_Key , NULL , DD_Dad_Key , NULL , KK_KidName , KK_Dad_Key FROM GG_Grandpas, DD_Dads , KK_Kids WHERE GG_Grandpa_Key = DD_Grandpa_Key AND DD_Dad_Key = KK_Dad_Key
FOR XML EXPLICIT
I've tried it all different ways, but no luck so far. Any ideas?
How do I get my data to show starting at the first row instead of skipping down?
Refer to the attachment.
Code: CREATE PROCEDURE [dbo].[uspReportData] -- Add the parameters for the stored procedure here @Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL, @Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,
I am redesigning an application that distributes heldesk tickets to our50 engineers automatically. When the engineer logs into their window astored procedure executes that searches through all open tickets andassigns a predetermined amount of the open tickets to that engineer.Theproblem I am running into is that if 2 or more engineers log in at thesame time the stored procedure will distribute the same set of ticketsmultiple times.Originally this was fixed by "reworking" the way SQL Server handlestransactions. The original developer wrote his code like this:-----DECLARE @RET_STAT INTSELECT 'X' INTO #TEMPBEGIN TRANUPDATE #TEMP SET 'X' = 'Y'SELECT TOP 1 @TICKET_# =TICKET_NUMBER FROM TICKETS WHERE STATUS = 'O'EXEC @RET_STAT = USP_MOVE2QUEUE @TICKET_#, @USERIDIF @RET_STAT <> 0ROLLBACK TRANRETURN @RET_STATENDCOMMIT TRAN-----The UPDATE of the #TEMP table forces the transaction to kick off andlocks the row in table TICKETS until the entire transaction hascompleted.I would like to get rid of the #TEMP table and start using isolationlevels, but I am unsure which isolation level would continue to lockthe selected data and not allow anyone else access. Do I need acombination of isolation level and "WITH (ROWLOCK)"?Additionally, the TICKETS table is used throughout the application andI cannot exclusively lock the entire table just for the distributionprocess. It is VERY high I/O!Thanks for the help.
I am trying to get my head around locking (row, table) and Isolation Levels. We have written a large .NET/SQL application and one day last week we had about two dozen people in our company do some semi "stress/load" testing of the app.
On quite a few occassions, a few of the users would receive the following error:
"Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
We are handling this on two fronts, the app and the database. The error handling in the app is being modified to capture this specific error and to retry the transaction.
However, from the database side, I am trying to find the most affective and efficient change to make regarding locking. I have been doing a lot of reading online and in BOL to get a better grasp of locking, but what I would really like is feedback from the community (forum) and get your thoughts on what changes I should make, if any, on the db side.
we are building a DW for a company that operates in 10 countries with the home country being the major portion of the data......
Previous efforts have always had the data separated by schemas and so to ask a question about a specific country required the schema number to be provided.
I am proposing that the 10 schemas, and therefore 10x the number of tables, indexes etc, be removed in favour of using partitioning.
However, we want to partition by country and by periods...that is we would like to create monthly partitions as normal.
No matter how I read the documentation and test this out, it seems to me that this multiple levels of partitioning can only be achieved if I create a field on the table that is some manipultion of the key for the company reporting structure and the period. I think I can take the first, add 10M and then add the period key.
But I am unsure if the optimiser is going to do it's partition elimination properly on such a calculated field.
Has anyone attempted such a multi-level partitioning scheme in SQL Server? I am thinking people must have as one level of partitioning was seen to be too restrictive many years ago.....
I suspect I know the answer but I'll ask away. We currently have our SSIS packages set up to log to SQL Server. Currently they log OnError, OnInformation and OnTaskFailed. If I'd like to have it log OnPipeLineRowsSent, is there anyway I can get that done without opening up the package and editing it? I know the change is trivial from the IDE but the deployment process at my current engagement is quite lengthy. If something breaks in production, I'd like to know if it'll be possible to turn up the chattiness of logging without going through a full deploy scenario.
I was looking at the parameters for dtexec/dtexecui and I see that you can configure where something logs but nothing about the verbosity of the logs generated. Is it something I'm missing with that or is that all you can set there?
The only other option that jumps out at me is to develop a custom script or component that sets the logging level based on a parameter. Anyone have a thought as to how much effort that would be---something easily tackled or probably more trouble that it's worth?
Is anyone aware if the database engine build levels will affect the mirroring process. we're in the process of upgrading a PROD environment to a new build however like to delay onto the disaster recovery (DR) server in case of issues. The DR is the mirror in the setup and so would have a differnet build level.
Is this likely to affect anything? All info seems to point to only versions differences causing a problem but not the build.
I am a newbie developing a web site. My local machine has sql 2005 standard edition on it. The host that I am trying to use only has sql 2005 64 bit version.
would I be able to do the use the same database between the two ( i.e. once my site is running at host, would I be able to download the database on my local machine, make changes to my site and then upload again on the hose without any glitches?). In addition, how about sql express doing the same with sql 2005 64 bit.
I have to spec out a new server, and I have the option of using RAID-5 or RAID-1 drive sets. I am limited to 24 drives in groups of 6, and I have to have one hotspare per group, so up to 5 usable drives per channel.
I need 80-100GB of space total.
Okay, you're waiting for the question....I have heard many differing opinions on which is better, RAID 1 or RAID 5. If I have 2 Large disks (say 36GB) on a Raid-1, I assume having 4 smaller 9GB drives on a RAID-5 will be faster, but I am not sure due to overhead and the like.
Does anyone know where I can get more information on RAID performance and how it is going to affect me? The database is going to be Read-Write, with a ton of small transactions, and the occasional (usually on a weekend) aggregation.
I have a Star schema based dimension called Customer which has these levels:
ALL Customers Level1: Customer Type Level2: Customer Sub Type Level3: Customer Name
When a user is browsing the cube, is it possible to hide the the 1st level (and all it's sub-levels)? For example, If the Customer Type = "Low Ranked" then I do not want it to be displayed to the user while (s)he is selecting from the dimension. HOWEVER I only want it to be hidden from being displayed but it's effect should always reflect e.g. Suppose: [list=1] Sales (measure count) for Customers with Type "High Ranked" = 100 Sales (measure count) for Customers with Type "Medium Ranked" = 50 Sales (measure count) for Customers with Type "Low Ranked" = 10 [/list=1] Now if the user selects 'ALL Customer Type' in the dimension he/she should get a total Sale (measure count) of 160 (i.e. 100+50+10).
However when the user expands the Customers Dimension (i.e. ALL Customers), the resulting child nodes should only list 2 nodes i.e. High Ranked and Medium Ranked.
I went to the cube editor --> Advanced Properties and looked at the 'Hide Member If' property but amongst the 5 options there is none which allows me to specify the criteria.
Maybe the solution already is in one of those 5 options and thus please help me.
We seem to have a problem with permission levels and connecting to an MSDE (MSSQL) server. If the user is under the Domain Admins group, the the access projet (front end) will open correctly and connect to the data server. If they are not part of that group then the front end can ever establish a file to the database server. We do not want to make all the users Domain Admins, so is there a way to make MSDE let them trough even though they are on a lower level.
I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.
Any ideas how how to make MSDE let anyone connect? Thanks in advance!
There will be three levels of data imposed at the Application Layer
Level 1: ParentID = 0 An Item Like Geography Level 2: ParentID = a Level 1 WikiID A sub Topic like Volcanoes Level 3: ParentID = Level 2 WikiID A bottom Topic like Pyroclastic Flows
I Need a SQL statement that Will Produce the Output where The output will be produced like this: Level 1 Level 2 Level 2 Level 2 Level 1 Level 2 Level 2
I Built this but its wrong and has no order by Group by Statements Select * from WikiData where ParentID = 0 or ParentID IN (Select * from WikiData where ParentID = 0)
The script below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.
This script returns 3 result sets. The first shows the tables in order by level and table name. The second shows tables and tables that reference it in order by table and referencing table. The third shows tables and tables it references in order by table and referenced table.
Tables at level 0 have no related tables, except self-references. Tables at level 1 reference no other table, but are referenced by other tables. Tables at levels 2 and above are tables which reference lower level tables and may be referenced by higher levels. Tables with a level of NULL may indicate a circular reference (example: TableA references TableB and TableB references TableA).
Tables at levels 0 and 1 can be loaded first without FK violations, and then the tables at higher levels can be loaded in order by level from lower to higher to prevent FK violations. All tables at the same level can be loaded at the same time without FK violations.
Tested on SQL 2000 only. Please post any errors found.
Edit 2006/10/10: Fixed bug with tables that have multiple references, and moved tables that have only self-references to level 1 from level 0.
This script finds table references and ranks them by level in order to be able to load tables with FK references in the correct order. Tables can then be loaded one level at a time from lower to higher. This script also shows all the relationships for each table by tables it references and by tables that reference it.
Level 0 is tables which have no FK relationships.
Level 1 is tables which reference no other tables, except themselves, and are only referenced by higher level tables or themselves.
Levels 2 and above are tables which reference lower levels and may be referenced by higher levels or themselves.
declare @table table ( TABLE_NAME nvarchar(200) not null primary key clustered ) set nocount off
print 'Load tables for database '+db_name()
insert into @table select TABLE_NAME = a.TABLE_SCHEMA+'.'+a.TABLE_NAME from INFORMATION_SCHEMA.TABLES a where a.TABLE_TYPE = 'BASE TABLE'and a.TABLE_SCHEMA+'.'+a.TABLE_NAME <> 'dbo.dtproperties' order by 1
print 'Load PK/FK references' insert into @r selectdistinct PK_TABLE = b.TABLE_SCHEMA+'.'+b.TABLE_NAME, FK_TABLE = c.TABLE_SCHEMA+'.'+c.TABLE_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b on a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and a.CONSTRAINT_NAME = c.CONSTRAINT_NAME order by 1,2
print 'Make copy of PK/FK references' insert into @rs select * from @r order by 1,2
print 'Load un-referenced tables as level 0' insert into @t select REF_LEVEL = 0, a.TABLE_NAME from @table a where a.TABLE_NAME not in ( select PK_TABLE from @r union all select FK_TABLE from @r ) order by 1
-- select * from @r print 'Remove self references' delete from @r where PK_TABLE = FK_TABLE
declare @level int set @level = 0
while @level < 100 begin set @level = @level + 1
print 'Delete lower level references' delete from @r where PK_TABLE in ( select TABLE_NAME from @t ) or FK_TABLE in ( select TABLE_NAME from @t )
insert into @t select REF_LEVEL =@level, a.TABLE_NAME from @table a where a.TABLE_NAME not in ( select FK_TABLE from @r ) and a.TABLE_NAME not in ( select TABLE_NAME from @t ) order by 1
if not exists (select * from @r ) begin print 'Done loading table levels' print '' break end
end
print 'Count of Tables by level' print ''
select REF_LEVEL, TABLE_COUNT = count(*) from @t group by REF_LEVEL order by REF_LEVEL
print 'Tables in order by level and table name' print 'Note: Null REF_LEVEL nay indicate possible circular reference' print '' select b.REF_LEVEL, TABLE_NAME = convert(varchar(40),a.TABLE_NAME) from @table a left join @t b on a.TABLE_NAME = b.TABLE_NAME order by b.REF_LEVEL, a.TABLE_NAME
print 'Tables and Referencing Tables' print '' select b.REF_LEVEL, TABLE_NAME = convert(varchar(40),a.TABLE_NAME), REFERENCING_TABLE =convert(varchar(40),c.FK_TABLE) from @table a left join @t b on a.TABLE_NAME = b.TABLE_NAME left join @rs c on a.TABLE_NAME = c.PK_TABLE order by a.TABLE_NAME, c.FK_TABLE
print 'Tables and Tables Referenced' print '' select b.REF_LEVEL, TABLE_NAME = convert(varchar(40),a.TABLE_NAME), TABLE_REFERENCED =convert(varchar(40),c.PK_TABLE) from @table a left join @t b on a.TABLE_NAME = b.TABLE_NAME left join @rs c on a.TABLE_NAME = c.FK_TABLE order by a.TABLE_NAME, c.PK_TABLE
