July 9, 2012 at 2:29 am
Hi all,
I need to write a query that, for a given DB, will show:
- all the tables
- every column for these tables
- the data type
- and 3 examples of every column, the examples should be the first elements of the column when the table is sorted by its Primary Key
So far, I made a query that does the first 3:
select TABLE_NAME , COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION
from INFORMATION_SCHEMA.COLUMNS
the last one seems really hard, can you please help?
Thank you,
Kol
July 9, 2012 at 2:56 am
you could try something along these lines
EXEC sp_MSForEachTable 'SELECT top 3 * FROM ?'
sp_MSForEachTable is undocumented...suggest you do some research.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 9, 2012 at 3:16 am
TOP doesn't guarantee ORDER BY. You should explicitly specify ORDER BY where possible and you can limit the result set like this:
...
ORDER BY yourCol ASC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY;
If you don't know the value of yourCol or you want to get it programmatically, you can either feed it in using a query that selects the columns that have PK constraints (loop it in a cursor, maybe) or you could use ORDER BY 1 if you are certain the PK col is always the 1st col returned.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
July 9, 2012 at 4:35 am
derek.colley (7/9/2012)
TOP doesn't guarantee ORDER BY. You should explicitly specify ORDER BY where possible and you can limit the result set like this:
...
ORDER BY yourCol ASC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY;
That won't work on SQL 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2012 at 6:35 am
The problem I am facing is that I cannot use the output of the first query I am running :
select TABLE_NAME , COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION
from INFORMATION_SCHEMA.COLUMNS
in a second query, I want to use the COLUMN_NAME and TABLE_NAME values to build a new query that does the following:
select COLUMN_NAME from TABLE_NAME (of course this won't work)
I tried creating a cursor with the first query and then going through it and building a new dynamic SQL query but it's a mess and doesn't work 🙁
DECLARE @table VARCHAR(50), @column VARCHAR(50), @type VARCHAR(50), @precision VARCHAR(50), @pk VARCHAR(50)
DECLARE result CURSOR FOR
select I.TABLE_NAME , I.COLUMN_NAME, I.DATA_TYPE, I.NUMERIC_PRECISION, C.CONSTRAINT_NAME
from INFORMATION_SCHEMA.COLUMNS as I, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as C
where C.CONSTRAINT_NAME like 'PK_%' and I.TABLE_NAME = C.TABLE_NAME
OPEN result
FETCH result INTO @table, @column, @type, @precision, @pk
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE ('SELECT ' + @column + ' FROM ' +@table)
FETCH result INTO @table, @column, @type, @precision, @pk
End
CLOSE result
DEALLOCATE result
any advice please ?
July 9, 2012 at 6:52 am
Define 'doesn't work'?
Dynamic SQL is about the only way you're going to do this without writing hundreds of SQL statements.
Curious, why information schema and not the catalog views (sys.tables, sys.columns, etc)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2012 at 2:26 am
It didn't provide any results for the dynamically built query, but it was because I didn't specify the schema, once corrected, the query ran fine.
Still I am having an issue with retrieving the "3 first values of a column when the table is sorted by its primary key (when this key exists, given it is a simple key)".
I managed to get the first one, is there a way to get the "second" and "third" values when a table is sorted ?
Thank you all in advance
July 10, 2012 at 2:30 am
Post your code.
Typically you'd use TOP (3), but I'm not sure what you're doing,so...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2012 at 2:39 am
Thank you, I found it, to share it with people, here is the trick
Thank you all again 🙂
July 10, 2012 at 2:50 am
If all you're trying to do is find the top 3 by the pk, that method is not what you want to use, it's overkill. It's for when you want the 3rd and only the 3rd, not the top 3.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2012 at 5:25 am
I dynamically built 3 different queries, one for the first, one for the second and one for the third and it worked fine
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply