- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 5074
USE AdventureWorks GO SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%EmployeeID%' ORDER BY schema_name, table_name;From here
- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 5412
EXEC sp_MSforeachtable @command1 = 'select * from ?'
- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 5504
If you are receiving error:
Invoke or BeginInvoke cannot be called on a control until the window handle has been created..
On new installation of MS SQL 2008 on Windows 7 64 bit, then try to download:
Cumulative Update Package 8 for SQL Server 2008
You don't need to have MS SQL Server installed.
Taken from here.
- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 5510
On the MS SQL forum I saw interesting question, which goes like this:
Hello. I am struggling with a query that I know can be done but I'm just not seeing it.
MeetState=state where the track meet was held
TeamName=name of the team (of course)
TeamState=home state of the team
MeetState TeamName TeamState
FL Houston TX
FL Tampa FL
FL Detroit MI
TX Dallas TX
TX Houston TX
TX Austin TX
NY Dallas TX
NY Tampa FL
NY Phoenix AZ
NY Albany NY
What I am needing is a list of the track meets that were attended by an out-of-state team, along with a comma-delimited list of those teams.
With the above data, the results would look like this:
FL Houston,Detroit
NY Dallas,Tampa,Phoenix
(the TX meet would not be listed because all of the teams were also from TX)
I know that for someone out there this is a simple task and I am looking forward to learning something new.
Thank you.
And, aswer is:
DECLARE @Sample TABLE
(
MeetState CHAR(2) NOT NULL,
TeamName VARCHAR(20) NOT NULL,
TeamState CHAR(2) NOT NULL
)
INSERT @Sample
(
MeetState,
TeamName,
TeamState
)
VALUES ('FL', 'Houston', 'TX'),
('FL', 'Tampa', 'FL'),
('FL', 'Detroit', 'MI'),
('TX', 'Dallas', 'TX'),
('TX', 'Houston', 'TX'),
('TX', 'Austin', 'TX'),
('NY', 'Dallas', 'TX'),
('NY', 'Tampa', 'FL'),
('NY', 'Phoenix', 'AZ'),
('NY', 'Albany', 'NY')
SELECT ms.MeetState,
STUFF(team.Name, 1, 1, '') AS TeamNames
FROM (
SELECT MeetState
FROM @Sample
WHERE MeetState <> TeamState
GROUP BY MeetState
) AS ms
CROSS APPLY (
SELECT DISTINCT TOP(2147483647)
',' + s.TeamName
FROM @Sample AS s
WHERE s.MeetState = ms.MeetState
AND s.TeamState <> ms.MeetState
ORDER BY ',' + s.TeamName
FOR XML PATH('')
) AS team(Name)
ORDER BY ms.MeetState
This just note to my self, I didn't test, but I maybe need it in the future.
---
Actually I found one better way:
SELECT
t1.TeamID,
MemberList = substring((SELECT ( ', ' + FirstName )
FROM TeamInfo t2
WHERE t1.TeamID = t2.TeamID
ORDER BY
TeamID,
FirstName
FOR XML PATH( '' )
), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID
From here.