For your entertainment...? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

For your entertainment...?

Author

Topic

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-17 : 11:15:04

Found this beauty while doing a routine status check. Formatted it from the original single line (shut up Femenella) and changed the database name, just in case the vendor actually reads anything about SQL. Everything else is the same:

SELECT "Col1010","Col1011","Col1012","Col1020","Col1021","Col1027","Col1015","Expr1008" 
  FROM (SELECT "Col1010","Col1011","Col1012","Col1019","Col1020","Col1021","Col1024",
		"Col1025","Col1026","Col1027","Col1015","Col1016","Col1017",'DB02' "Expr1008" 
    FROM (SELECT "Tbl1001"."name" "Col1010","Tbl1001"."id" "Col1011","Tbl1001"."xtype" "Col1012" 
      FROM "DB02"."dbo"."sysobjects" "Tbl1001" 
WHERE NOT "Tbl1001"."name" like N'sys%' COLLATE SQL_Latin1_General_CP1_CI_AS
 AND NOT "Tbl1001"."name" like N'df__sys%' COLLATE SQL_Latin1_General_CP1_CI_AS
 AND NOT "Tbl1001"."name" like N'df__MS%' COLLATE SQL_Latin1_General_CP1_CI_AS
 AND NOT "Tbl1001"."name" like N'syncobj%' COLLATE SQL_Latin1_General_CP1_CI_AS
 AND NOT "Tbl1001"."name" like N'msreplication%' COLLATE SQL_Latin1_General_CP1_CI_AS
 AND NOT "Tbl1001"."name" like N'mspeer%' COLLATE SQL_Latin1_General_CP1_CI_AS
 AND NOT "Tbl1001"."name" like N'mspub%' COLLATE SQL_Latin1_General_CP1_CI_AS
 AND NOT "Tbl1001"."name" like N'mssnapshot%' COLLATE SQL_Latin1_General_CP1_CI_AS
 AND NOT "Tbl1001"."name" like N'mssubscription%' COLLATE SQL_Latin1_General_CP1_CI_AS
) Qry1013,
          (SELECT "Col1019","Col1020","Col1021","Col1024","Col1025","Col1026","Col1027","Tbl1007"."name" "Col1015",
			"Tbl1007"."id" "Col1016","Tbl1007"."colid" "Col1017" 
            FROM "DB02"."dbo"."syscolumns" "Tbl1007",
            (SELECT "Col1019","Col1020","Col1021","Tbl1005"."id" "Col1024","Tbl1005"."indid" "Col1025",
				"Tbl1005"."colid" "Col1026","Tbl1005"."keyno" "Col1027" 
FROM (SELECT "Tbl1003"."id" "Col1019","Tbl1003"."indid" "Col1020","Tbl1003"."name" "Col1021" 
                FROM "DB02"."dbo"."sysindexes" "Tbl1003" 
                WHERE NOT "Tbl1003"."name" like N'%_dta_%' COLLATE SQL_Latin1_General_CP1_CI_AS
) Qry1022,
"DB02"."dbo"."sysindexkeys" "Tbl1005" 
WHERE "Col1019"="Tbl1005"."id" AND "Col1020"="Tbl1005"."indid") Qry1028 
WHERE "Col1024"="Tbl1007"."id" AND "Col1026"="Tbl1007"."colid") Qry1029 
WHERE "Col1011"="Col1016") Qry1030 
ORDER BY "Col1010" ASC, "Col1021" ASC, "Col1027" ASC, "Col1015" ASC

Really people, is it THAT HARD to learn how to use JOIN? I could generate better SQL by getting drunk, barfing on my keyboard, and wiping it up...with my cat...clawing and fighting me the whole time.

And no, the typical bullshit line about "cross-platform SQL" doesn't fly, since they're querying SQL Server system objects and using the COLLATE option (totally unnecessary BTW).

mfemenel
Professor Frink

1421 Posts

Posted - 2010-11-17 : 11:18:11

"I could generate better SQL by getting drunk, barfing on my keyboard, and wiping it up...with my cat...clawing and fighting me the whole time."

I've read your code this explains so much!

Mike
"oh, that monkey is going to pay"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-11-17 : 11:42:29

That looks like something MS Access would generate.

Maybe that app was "upsized"?

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-17 : 12:00:59

quote:
Originally posted by Michael Valentine Jones

That looks like something MS Access would generate.

Maybe that app was "upsized"?

CODO ERGO SUM