November 6, 2012 at 7:51 am
I am new to programming and have had trouble trying to create a sql statement that gives me the results I need. I have tried to use JOIN and UNION statements to accomplish this with no success so far. I think using a loop would be helpful but my coworker told me I could do this without using a loop. Any suggestions?
I have two seperate select sql statements that are finding two pieces of information that needs to be combined into one:
1) statement finds all the databases in a given instance that begin with letter 'k'
ex: SELECT name FROM sys.databases Where Name like 'k%' Order by name
2) finds the number of coulmns that are in the 'Hist' table in one of the databases
ex: USE Kary
SELECT Count(*)As Columns
FROM Sys.SysColumns
Where ID =
(SELECT ID FROM Sys.SysObjects Where Name = 'Hist')
I need these two statements to be combined in a way that will search through these particular databases and tell me the number of columns in the Hist table for each database in one result set. Thank you in advance, have a great day
November 6, 2012 at 7:59 am
For this kind of "look in every database" script, using a cursor to step through the list of databases you want is usually your best bet.
Since you would need to use 3-part names on sys.columns (<database name>.sys.columns) and on object_id, and you can't define objects in queries that way without using dynamic SQL, you're pretty much stuck using a cursor (or other loop) to generate dynamic SQL, and the running that.
Most of the time, cursors/loops are a poor choice, but for this kind of thing, they're pretty much standard.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 6, 2012 at 9:56 am
Any good idea of how that might look? I have created triggers but haven't had any experience working with cursors yet..
November 6, 2012 at 10:36 am
This should get you close.
EXEC sp_MSForEachDB 'Use ?; select table_catalog, table_name, count(*) from information_schema.columns
where table_catalog like ''k%''
and table_name = ''hist''
group by table_catalog, table_name'
November 6, 2012 at 11:12 am
Not sure why you'd connecting triggers and cursors. They're completely different subjects.
But, here's a sample script that should do what you need:
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
DECLARE Cur CURSOR FAST_FORWARD LOCAL
FOR
SELECT DB.name
FROM sys.databases AS DB
WHERE DB.name LIKE 'K%';
DECLARE @DB SYSNAME,
@sql NVARCHAR(MAX);
CREATE TABLE #T
(DB SYSNAME PRIMARY KEY,
Cols INT);
OPEN Cur;
FETCH NEXT FROM Cur INTO @DB;
WHILE @@FETCH_STATUS = 0
BEGIN;
SET @sql = 'USE ' + QUOTENAME(@DB)
+ ';INSERT INTO #T (DB, Cols) SELECT DB_NAME(), COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID('''
+ QUOTENAME(@DB) + '.dbo.Hist'');';
EXEC sp_executeSQL @sql;
FETCH NEXT FROM Cur INTO @DB;
END;
CLOSE Cur;
DEALLOCATE Cur;
SELECT *
FROM #T;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 6, 2012 at 11:49 am
Not making a connection btwn the two concepts, simply stating what I have had experience with so far using SQL Server. Thank you so much for the help, I am in the process of trying the script out now
November 6, 2012 at 11:56 am
benjamin.reyes
Script you provided works perfect, gonna make some minor changes but that is exactly what I was looking for. Thank you!!!
November 6, 2012 at 12:08 pm
Awsome. Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 6, 2012 at 12:09 pm
Keep in mind sp_MSforeachdb is an undocumented stored procedure, and may be removed in future versions. If this is for actual production development please use what GSquared provided.
November 6, 2012 at 12:46 pm
Sounds good to me, thanks again guys
November 6, 2012 at 7:29 pm
As noted, it can also be done with dynamic SQL.
DECLARE @TableName VARCHAR(100) = 'Hist'
,@SQL NVARCHAR(MAX)
;WITH CountCols AS (
SELECT name, MySQL=' UNION ALL ' + '
SELECT DatabaseName=a.TABLE_CATALOG COLLATE database_default, [CountOfCols ' +
@TableName + ']=COUNT(*)
FROM [' + name + '].INFORMATION_SCHEMA.TABLES a
INNER JOIN [' + name + '].INFORMATION_SCHEMA.COLUMNS b
ON a.TABLE_CATALOG = b.TABLE_CATALOG AND
a.TABLE_SCHEMA = b.TABLE_SCHEMA AND
a.TABLE_NAME = b.TABLE_NAME
WHERE a.TABLE_NAME = @TableName
GROUP BY a.TABLE_CATALOG '
FROM sys.databases
WHERE name LIKE 'K%')
SELECT TOP 1 @sql=STUFF((
SELECT MySQL + ''
FROM CountCols b
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 11, '')
FROM CountCols a
EXEC sp_executesql @sql
,N'@TableName VARCHAR(100)', @TableName = @TableName
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply