Query for Table structure

Hi,
I'm looking for some code that will pull back a list all tables in a database that meet a certain requirement - in this case all tables that have an identity column.
Can anyone point me in the right direction?
Cheers
Gregselect object_name(id),* from syscolumns
where autoval is not null

Query Editor: how to display individual row vertically

In query editor I displayed a single row from a table. The row is so long that I need to scroll horizontally back and forth to check out it's fields. Using t-sql (or otherwise) can I display the row like this: (vertically)

\

Field Name 1: < data value 1>

Field Name 2: < data value 2>

Field Name 3: < data value 3>

Field Name 4: < data value 4>

etc.

TIA,

barkingdog

Dog:

If you are using SQL Server 2005, you ought to be able to use an UNPIVOT

Dave

|||

Dog:

That SQL 2005 code might look something like this:

-- -
-- In order for UNPIVOT to work, the data must be homogeneous.
-- For this reason, all of the fields are transformed into a
-- varchar (40) string.
--
-- Also note the ISNULL function on field 3. If this is not
-- done, this row will not be displayed.
-- -
select convert (varchar (25), FieldName + ':') as FieldName,
FieldValue
from ( select convert (varchar (40), 'This is a test.')
as [Field Name 1],
convert (varchar (40), 'This is the 2nd field.')
as [Field Name 2],
convert (varchar (40), isnull (null, '[Null]'))
as [Field Name 3],
convert (varchar (40), 45.27)
as [Field Name 4]
) x
unpivot (FieldValue for FieldName
in ( [Field Name 1], [Field Name 2],
[Field Name 3], [Field Name 4]
)
) as xx

--
-- Sample Output:
--

-- FieldName FieldValue
-- - -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- Field Name 3: [Null]
-- Field Name 4: 45.27

-- (4 row(s) affected)

|||

If you are using SQL 2000 you might try something like:

select 'Field Name 1:' as FieldName,
'This is a test.' as fieldValue
union all
select 'Field Name 2:',
'This is the 2nd field.'
union all
select '...', ' '
union all
select 'Field Name N: ', 'Nth piece of data'

--
-- Sample Output:
--

-- FieldName fieldValue
-- -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- ...
-- Field Name N: Nth piece of data

-- (4 row(s) affected)

Query DB2 from SSRS and Convert Integer to Time

Hi,

I am querying DB2 from SSRS. I get an interger back that represents a time like this: HHMMSS

(The data type in DB2 is an integer.) I would like to make this representation of the time display a little more friendly. Does anyone have a good idea on how I can change the query or SSRS format to display semi-colons to break up the hours, minutes and seconds? Also, the other issue is that since I don't get leading zeros back....they probably need to be added to this value if the value is not a full 6 characters.

Here is one approach to this issue.

DECLARE @.MyTime int
SET @.MyTime = 63000

SELECT cast( stuff( stuff( right( '0' + cast( @.MyTime AS varchar(6) ), 6 ), 3, 0 , ':'), 6, 0, ':' ) AS datetime )

If this is a regular occurance, you may wish to create a FUNCTION for this task.

|||

Thanks. It looks like DB2 does not like the STUFF command, so I will try to apply this concept after I find a similar DB2 command.

Query based parameter value changes back to default value

I have several parameters on my report, some have non-queried lists, and
some have queried lists. All of them have default values. When I ran
reports on the Report Manager, I changed the value of the parameters from
the drop-down list to produce different reports. My problem is whenever I
changed the value of a parameter(pA), the value of those queried-based
parameters that were listed after pA would switch back to their respective
default values. This didn't happen when I previewed reports on Visual
Studio .Net Development Environment. Is this a bug, limitation, or am I
missing something?
Any help would be appreciated.
Paul
--
Message posted via http://www.sqlmonster.comPaul,
It sounds like your default values are calculated values (begin with a '=').
The way that RS computes these is that whenever you change a parameter, it
recalculates all the calculated parameters after it. This behaviour is to
allow cascading parameters. As far as I know, there is no way to turn this
calculation off. However, I worked around it by creating another dataset
which had a column for each default value (I do a lot of default dates). You
can then set the default values for the parameters to the dataset value in
the parameters window. I have to use SQL to do the calculations, but for
most things, this should be an easy workaround. It gets rid of the changing
values and also the refresh that takes place when you change calculated
parameters
"Paul via SQLMonster.com" wrote:
> I have several parameters on my report, some have non-queried lists, and
> some have queried lists. All of them have default values. When I ran
> reports on the Report Manager, I changed the value of the parameters from
> the drop-down list to produce different reports. My problem is whenever I
> changed the value of a parameter(pA), the value of those queried-based
> parameters that were listed after pA would switch back to their respective
> default values. This didn't happen when I previewed reports on Visual
> Studio .Net Development Environment. Is this a bug, limitation, or am I
> missing something?
> Any help would be appreciated.
> Paul
> --
> Message posted via http://www.sqlmonster.com
>|||Hi David, that works great. Thank you very much!!
Paul
--
Message posted via http://www.sqlmonster.com