List availables tables: columns

Showing posts with label columns. Show all posts

Friday, March 30, 2012

Load Excel Data using DTS NULL error

Hi!
I am loading data from Excel file, with three columns Varchar(5), Float and
Char(6).
When I execute DTS I am getting few NULL values for first column
(Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
Preview, it is also showing Null values for that.
Thanks,
SamNot sure what your question us here? Do you mean there arent really nulls
but sql thinks there is? Do you mean there are null values and sql wont
allow them?
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
> Hi!
> I am loading data from Excel file, with three columns Varchar(5), Float
> and
> Char(6).
> When I execute DTS I am getting few NULL values for first column
> (Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
> Preview, it is also showing Null values for that.
> Thanks,
> Sam|||Chris,
Thanks for the reply. This problem got fix temporarily, as the person who
gave me this excel file, he said there was some formula involved. Anyway the
problem I am describing below:
Example:
Column1 - Location list 1, 2, 3, ...10.
Column 2 - Location Name A, B, C,...D
I tried to load both columns and found in the preview that 3 and 7 are
giving NULL values.
Thanks,
Sam
"ChrisR" wrote:
> Not sure what your question us here? Do you mean there arent really nulls
> but sql thinks there is? Do you mean there are null values and sql wont
> allow them?
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
> > Hi!
> > I am loading data from Excel file, with three columns Varchar(5), Float
> > and
> > Char(6).
> > When I execute DTS I am getting few NULL values for first column
> > (Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
> > Preview, it is also showing Null values for that.
> >
> > Thanks,
> > Sam
>
>|||In your DTS Package, use an Active X Script to replace the NULL's.
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:022D6103-CB66-468A-8063-CEB75BB9FAEF@.microsoft.com...
> Chris,
> Thanks for the reply. This problem got fix temporarily, as the person who
> gave me this excel file, he said there was some formula involved. Anyway
> the
> problem I am describing below:
> Example:
> Column1 - Location list 1, 2, 3, ...10.
> Column 2 - Location Name A, B, C,...D
> I tried to load both columns and found in the preview that 3 and 7 are
> giving NULL values.
> Thanks,
> Sam
>
> "ChrisR" wrote:
>> Not sure what your question us here? Do you mean there arent really nulls
>> but sql thinks there is? Do you mean there are null values and sql wont
>> allow them?
>>
>> "Sam" <Sam@.discussions.microsoft.com> wrote in message
>> news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
>> > Hi!
>> > I am loading data from Excel file, with three columns Varchar(5), Float
>> > and
>> > Char(6).
>> > When I execute DTS I am getting few NULL values for first column
>> > (Varchar(5)). There is a appropriate data in Excel file. I checked in
>> > DTS
>> > Preview, it is also showing Null values for that.
>> >
>> > Thanks,
>> > Sam
>>|||Chris,
I tried with SQL Task and it did work.
Thanks,
Sam
"ChrisR" wrote:
> In your DTS Package, use an Active X Script to replace the NULL's.
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:022D6103-CB66-468A-8063-CEB75BB9FAEF@.microsoft.com...
> > Chris,
> > Thanks for the reply. This problem got fix temporarily, as the person who
> > gave me this excel file, he said there was some formula involved. Anyway
> > the
> > problem I am describing below:
> > Example:
> > Column1 - Location list 1, 2, 3, ...10.
> > Column 2 - Location Name A, B, C,...D
> > I tried to load both columns and found in the preview that 3 and 7 are
> > giving NULL values.
> >
> > Thanks,
> > Sam
> >
> >
> > "ChrisR" wrote:
> >
> >> Not sure what your question us here? Do you mean there arent really nulls
> >> but sql thinks there is? Do you mean there are null values and sql wont
> >> allow them?
> >>
> >>
> >> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> >> news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
> >> > Hi!
> >> > I am loading data from Excel file, with three columns Varchar(5), Float
> >> > and
> >> > Char(6).
> >> > When I execute DTS I am getting few NULL values for first column
> >> > (Varchar(5)). There is a appropriate data in Excel file. I checked in
> >> > DTS
> >> > Preview, it is also showing Null values for that.
> >> >
> >> > Thanks,
> >> > Sam
> >>
> >>
> >>
>
>

Load Excel Data using DTS NULL error

Friday, March 23, 2012

Listing words with no of occurances in sql server

Hi,
Is it possible to get words from text columns in a sql server database in the order of their occurances in that column with full text search or by any other method.
Thanks in advance.try this approach -- Simple Keyword Relevance (http://r937.com/keyword_relevance.html)|||:cool:

My query is regarding retreiving words from table on the bases of its maximum occurances in column irrespective of a row.

means a word that appears maximum number of times in a column, suppose if i have 10 rows in a table and word "Globe" appears maximum no of times say in 5 rows i want Globe word on the top.

Thanks in advance.|||select wordcolumn
, count(*) as occurrences
from daTable
group
by wordcolumn
order
by occurrences desc|||Hi,
Thanks for quick reply...

This could solve my problem if reffered column contain only single word at a time.
But this column is varchar column containing text of multiple words.

Any suggestion?

Thanks|||ok, that's going to require that you split out every word in the column individually

this may require a cross join with a numbers table

what's this for? how often are you going to use it?

the query will definitely not be easy to write, nor efficient to run|||Please read the sticky at the top of the board and post what it asks for...we need some examples|||you gonna write the query, brett?

here's an example --create table somewords
( id integer not null primary key identity
, blah text not null
);
insert into somewords (blah)
values ('a word that appears maximum number of times in a column')
insert into somewords (blah)
values ('Is it possible to get words from text columns in a sql server database')
insert into somewords (blah)
values ('This could solve my problem if reffered column contain only single word')
insert into somewords (blah)
values ('that''s going to require that you split out every word in the column individually')
insert into somewords (blah)
values ('the query will definitely not be easy to write')
insert into somewords (blah)
values ('Please read the sticky at the top of the board')
insert into somewords (blah)
values ('The physical order of data in a database has no meaning')i'm sure you understand the requirement -- which word occurs the most frequent?|||Hi,

r937 quoted the right example and i am sure he understood the problem.

I need it often in my application.

If you say to split words in multiple columns, the problem here is we are not sure about maximum number of words in field.|||Is this for doing a search and ranking the results based on multiple search criteria...if it is then Rudy did not understan...because what you are asking makes no sense

Is it really show me for all rows in a table, the rows with the most occuring types?

If it is, and you need to "do that alot", what in heavens name for?|||Oh, and why would you need a query

http://www.google.com/search?hl=en&lr=&safe=off&q=a&btnG=Search

About 8 billion hits

I think that would "win"|||the question is really very simple, brett

refer to the sample data which i posted (which you wanted to see), and answer the question: which word occurs most frequently in the blah column? hint: it isn't the word "data" and it isn't the word "word"|||OK, well, you would have to start be decontructig the data...but I still don't know what for or why there would be a need for this

create table somewords
( id integer not null primary key identity
, blah text not null
);
insert into somewords (blah)
values ('a word that appears maximum number of times in a column')
insert into somewords (blah)
values ('Is it possible to get words from text columns in a sql server database')
insert into somewords (blah)
values ('This could solve my problem if reffered column contain only single word')
insert into somewords (blah)
values ('that''s going to require that you split out every word in the column individually')
insert into somewords (blah)
values ('the query will definitely not be easy to write')
insert into somewords (blah)
values ('Please read the sticky at the top of the board')
insert into somewords (blah)
values ('The physical order of data in a database has no meaning')
GO

CREATE TABLE UniqueWords (
Word varchar(256)
, WordId int IDENTITY(1,1)
, Add_Dt datetime DEFAULT (GetDate()))
GO

CREATE UNIQUE INDEX UnqueWords_PK ON UniqueWords(Word)
GO

CREATE PROC isp_INS_UNIQUE_WORDS
AS
BEGIN
SET NOCOUNT ON
DECLARE @.Words INT, @.Pos INT, @.x Int, @.str varchar(256)
, @.word varchar(256), @.start int, @.end int, @.exitstart int
SELECT @.Words = 0, @.Pos = 1, @.x = -1, @.Word = '', @.start = 1

DECLARE myCursor CURSOR FOR SELECT Blah FROM SomeWords
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.str

WHILE @.@.FETCH_STATUS = 0
BEGIN
WHILE (@.x <> 0)
BEGIN
SET @.x = CHARINDEX(' ', @.str, @.Pos)
IF @.x <> 0
BEGIN
SET @.end = @.x - @.start
SET @.word = SUBSTRING(@.str,@.start,@.end)
IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @.Word)
INSERT INTO UniqueWords(Word) SELECT @.word
-- SELECT @.Word, @.@.ROWCOUNT,@.@.ERROR
-- SELECT @.x, @.Word, @.start, @.end, @.str
SET @.exitstart = @.start + @.end + 1
SET @.Pos = @.x + 1
SET @.start = @.x + 1
SET @.Words = @.Words + 1
END
IF @.x = 0
BEGIN
SET @.word = SUBSTRING(@.str,@.exitstart,LEN(@.str)-@.exitstart+1)
IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @.Word)
INSERT INTO UniqueWords(Word) SELECT @.word
-- SELECT @.Word, @.@.ROWCOUNT,@.@.ERROR
-- SELECT @.x, @.Word, @.exitstart, LEN(@.str)-@.exitstart, @.str
END
END
FETCH NEXT FROM myCursor INTO @.str
SELECT @.Words = 0, @.Pos = 1, @.x = -1, @.Word = '', @.start = 1
END

CLOSE myCursor
DEALLOCATE myCursor
SET NOCOUNT OFF
RETURN @.Words
END
GO

EXEC isp_INS_UNIQUE_WORDS
GO

SELECT * FROM UniqueWords ORDER BY Word
GO

DROP PROC isp_INS_UNIQUE_WORDS
DROP TABLE UniqueWords, somewords
GO

I'm gonna blog this|||...but I still don't know what for or why there would be a need for this

You don't think there's a use for data mining on simple text?

let's see...

military intel
or
political speech analysis
or
most occurences in scientific notation
...|||brett! dude!! that's wonderful! and the cursors, so pretty!!

now, any chance you could actually address the problem as initially posted?

which word occurred the most often?

boy it's taking you a long time to catch on

:)|||boy it's taking you a long time to catch on

:)

Well, I'll have to step back aand rethink this then

I thought you would need a list of unique words in another table, then perhaps join to it...and something else...don't know...thought the deconstructing thing was a fun puzzle, of which I would have no other way to do that

I was think of something like

...oh jeez, I don't know what I was thinking|||well...I guess I don't know what I'm talking about...especially sice the req is not very clear

Wednesday, March 21, 2012

listing columns in view and stored procedures

Using SS2000 SP4. If I create a view and list the columns in the select
statement, if I then create a sp using the view is it better to list the
columns again or just use '*'?
Thanks,
--
Dan D.Do not use *. You will not know what you are selecting if your view
definition is changed.
"Dan D." wrote:

> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||You will get varying opinions here, especially since we don't know what your
definition of "better" is.
My opinion:
*ALWAYS* list your columns in production code, and never use SELECT *.
Too many things can go wrong throughout the pipeline.
A
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Good point. Thanks.
--
Dan D.
"Omnibuzz" wrote:
> Do not use *. You will not know what you are selecting if your view
> definition is changed.
> --
>
>
> "Dan D." wrote:
>|||That's how I feel. I saw a piece of code that was written using '*' and
wondered what other people thought.
Thanks,
--
Dan D.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You will get varying opinions here, especially since we don't know what yo
ur
> definition of "better" is.
> My opinion:
> *ALWAYS* list your columns in production code, and never use SELECT *.
> Too many things can go wrong throughout the pipeline.
> A
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||The person that wrote this should be shackled and whipped.
Since this is probably illegal in most states, provinces and countries...
He should at least be forced to write 100 times on a blackboard:
I shall never use "Select *" in production code again.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
> That's how I feel. I saw a piece of code that was written using '*' and
> wondered what other people thought.
> Thanks,
> --
> Dan D.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||I'll see if I can track him/her down.:)
--
Dan D.
"Raymond D'Anjou" wrote:

> The person that wrote this should be shackled and whipped.
> Since this is probably illegal in most states, provinces and countries...
> He should at least be forced to write 100 times on a blackboard:
> I shall never use "Select *" in production code again.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
>
>|||To add to the topic.
We now have a new company standard.. that you list ALL FIELDS in your INSERT
statements.
For Example:
at one point , an Emp table has EmpID, LastName, FirstName columns.
We had code like this
INSERT INTO Emp Values (101, 'Smith', 'John')
...
Why is this bad'
Someone adds a new column
Emp.Age.
Now every INSERT fails. Because the table has 4 columns, and the INSERT
supplies 3.
..
I can't tell you how many bugs I've tracked down with that (stupid) issue.
ALWAYS use a list. If it wasn't for quick debugging , Select * should be
outlawed! (Maybe a little extreme, but it can cause alot of issues)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Thanks for your 2cents Sloan.
--
Dan D.
"sloan" wrote:

> To add to the topic.
> We now have a new company standard.. that you list ALL FIELDS in your INSE
RT
> statements.
> For Example:
> at one point , an Emp table has EmpID, LastName, FirstName columns.
> We had code like this
> INSERT INTO Emp Values (101, 'Smith', 'John')
> ...
> Why is this bad'
> Someone adds a new column
> Emp.Age.
> Now every INSERT fails. Because the table has 4 columns, and the INSERT
> supplies 3.
> ...
> I can't tell you how many bugs I've tracked down with that (stupid) issue.
> ALWAYS use a list. If it wasn't for quick debugging , Select * should be
> outlawed! (Maybe a little extreme, but it can cause alot of issues)
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||On Mon, 1 May 2006 10:23:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>Using SS2000 SP4. If I create a view and list the columns in the select
>statement, if I then create a sp using the view is it better to list the
>columns again or just use '*'?
>Thanks,
Aaron promised you varying opinions, but everyone was taking the same
side, so I figure it was time to add my two cents.
I consider * to be a very valuable tool in the SELECT list, and
prefer it in many situations. In general I prefer * when the query
MUST include EVERY column from a table. It avoids the possible error
of leaving a column out, and it enforces a uniform sequence to the
columns that can't hurt.
Example: A view that has to include every column from a table, plus
other columns:
SELECT X.*, Y.SomeCol
If table X changes, all the is needed is to ALTER the view (with no
changes) to force a recompile.
When there are two tables with identical layouts and rows are inserted
from one into the other:
INSERT X
SELECT * FROM Y
It is very hard to get that wrong. Again, if the tables change all
that is required is a recompile, removing one more chance to make an
error. If only one of the tables change the recompile will fail,
which is a Good Thing as the issue of how to deal with the change was
not addressed, and needs to be. I prefer such a failure to having the
difference remain unadressed.
Roy Harvey
Beacon Falls, CT

Listing all tables and their columns?

Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.

I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.

Any help would be greatly appreciated. Thanks!Select Column_name,table_name From Information_schema.columns|||excellent, thanks!sql

Monday, March 12, 2012

List only the visible columns in a view?

In SQL Server 2005, when using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE I get a
row for every column in a view including those that might not be visible but
used only in the WHERE or ORDER BY clauses for example.
Is there an easy way to reduce the list to only show the columns that
actually appear in the SELECT clause of the view definition?
I've tried examining Profiler when expanding the columns node under the View
in Management Studio as it seems to load only one node per visible column
but haven't been able to make much sense of how it does this.
BTW I'm using that view as it promises to only show views and columns "to
which the current user has permissions". Is this reliable or is there a
preferred way of listing visible views, talbe, & columns etc?
cheers,
Paul.Hi, Paul
Look in the INFORMATION_SCHEMA.COLUMNS view.
Razvan|||Use the catalog schema views...
SELECT
name
FROM
sys.columns
WHERE
object_id = OBJECT_ID('dbo.YourViewName');
"Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2005, when using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE I get
> a
> row for every column in a view including those that might not be visible
> but
> used only in the WHERE or ORDER BY clauses for example.
> Is there an easy way to reduce the list to only show the columns that
> actually appear in the SELECT clause of the view definition?
> I've tried examining Profiler when expanding the columns node under the
> View
> in Management Studio as it seems to load only one node per visible column
> but haven't been able to make much sense of how it does this.
> BTW I'm using that view as it promises to only show views and columns "to
> which the current user has permissions". Is this reliable or is there a
> preferred way of listing visible views, talbe, & columns etc?
> cheers,
> Paul.
>|||Thanks Aaron - much appreciated.
Is there an easy way to determine which columns a user might have access to
when using the "sys" tables? That was what the INFORMATION_SCHEMA views
seemed to promise although there was some doubt in newsgroups as to their
accuracy in SQL2000 due to them being derived from the sysdepends table.
I'm betting that this will have changed in 2005.
However moving back to the sys tables/views will mean I have to determine
this permission information in some other way. Any seggestions would be
appreciated tremendously.
cheers,
Paul.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCq14hWBGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Use the catalog schema views...
> SELECT
> name
> FROM
> sys.columns
> WHERE
> object_id = OBJECT_ID('dbo.YourViewName');
>
> "Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
> message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
>|||> Is there an easy way to determine which columns a user might have access
> to when using the "sys" tables?
Take a look at sys.database_permissions
I believe the join between sys.database_permissions p and sys.comments c
would be:
ON
c.object_id = p.major_id
AND c.column_id = p.minor_id
WHERE
p.class = 1
AND p.grantee_principal_id = SUSER_ID('username');
I am not 100% sure on that, and don't have time today to experiment with
column-level permissions. But hopefully that gets you started.

> accuracy in SQL2000 due to them being derived from the sysdepends table.
Permissions derived from sysdepends? I think that is inaccurate.
A

Wednesday, March 7, 2012

list of columns in a table

Hi All,

I have a simple question, I want to get a list of all columns in a given table. result shouldn't give me rows. so even if table is huge the query runs quickly. what's the code for this

thanks

sonny

One way is something like:

Code Snippet

select c.colid,

c.name

from sysobjects o,

syscolumns c

where o.id = c.id

and o.name = 'nameOfYourTable'

order by c.name

|||

Another way is:

select Column_Name from INFORMATION_SCHEMA.Columns where Table_Name='Tablename'

|||

Hi kent,

that worked. Thanks.

Sonny

|||

Hi Mobin,

This is improvement sinc the columns are sorted as well.

thanks

sonny

List of columns from tables across databases.

Hey guys,

Couldn't find this anywhere in google.

I want a list of all database column names for a specific table/view
from across database.

I tried this...
----------------
Select *
>From Information_Schema.Columns
----------------

I also tried this...

----------------
select syscolumns.name, sysobjects.name, * from syscolumns, sysobjects
where
sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
----------------

These queries return information about the CURRENT database.

But, if I want to do it ACROSS database or across servers.. how can I
do this?

I will express my gratitude to everyone who is kind enough to answer
this question. (I've been stuck with this problem for a while now.)

Thanks!

OhMyGaw!Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_schema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

--
David Portas
SQL Server MVP
--

list of columns

Hi!
How can I use a transact SQL to get a list of columns for a specific
table withing a specific database?
Thank you,
T.
There is a variety of ways. You can use:
EXEC sp_columns 'tblname'
or query the information schema view:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' ;
or even
EXEC sp_help tablename ;
Anith
|||One more way (SQL Server 2005):
SELECT O.name AS table_name,
C.name AS column_name,
SCHEMA_NAME(O.schema_id) AS 'schema_name'
FROM sys.objects AS O
JOIN sys.columns AS C
ON O.object_id = C.object_id
WHERE O.type IN ('U')
AND O.name = 'YourTableName';
HTH,
Plamen Ratchev
http://www.SQLStudio.com

list of columns

Hi!
How can I use a transact SQL to get a list of columns for a specific
table withing a specific database?
Thank you,
T.There is a variety of ways. You can use:
EXEC sp_columns 'tblname'
or query the information schema view:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' ;
or even
EXEC sp_help tablename ;
--
Anith|||One more way (SQL Server 2005):
SELECT O.name AS table_name,
C.name AS column_name,
SCHEMA_NAME(O.schema_id) AS 'schema_name'
FROM sys.objects AS O
JOIN sys.columns AS C
ON O.object_id = C.object_id
WHERE O.type IN ('U')
AND O.name = 'YourTableName';
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Friday, February 24, 2012

List Box - Multiple Columns

Hi,
Any ideas on how to split data from a row set over two columns in a list box or table?
For example I have a data set with 10 rows and I want to display 5 rows in one column and the other 5 in the second column.
I tried creating two tables and filtering the first table to only show the even rows and the second to show odd rows. However the RowNumber(Nothing) function is not allowed in filters - 'RowNumber cannot be used in filters'
Thanks
KevinCould you use a multi-column report and limt the number of rows in each
column?
The major restriction is that you must use a "paged" rendered (PDF, Print
Preview) to see the multiple columns. Following your original posting is a
sample RDL that shows how this is done.
Note the table group expression.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:8512662A-BEDB-4194-8072-03CFFC7E8736@.microsoft.com...
> Hi,
> Any ideas on how to split data from a row set over two columns in a list
box or table?
> For example I have a data set with 10 rows and I want to display 5 rows in
one column and the other 5 in the second column.
> I tried creating two tables and filtering the first table to only show the
even rows and the second to show odd rows. However the RowNumber(Nothing)
function is not allowed in filters - 'RowNumber cannot be used in filters'
> Thanks
> Kevin
--
Sample Multi-Column Report - 5 records per column
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<PageHeader>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Franklin Gothic Medium</FontFamily>
<FontSize>12pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.11458in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.25in</Height>
<Width>3in</Width>
<CanGrow>true</CanGrow>
<Value>Sample Multiple Column Report</Value>
<Left>1.5in</Left>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.5in</Height>
</PageHeader>
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>11pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Info</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<rd:DefaultName>Country</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.75in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.5in</Height>
<Columns>2</Columns>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>7bec244d-832e-4036-9143-257dd0f8dcaa</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>2.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT CompanyName, City, Country
FROM Customers</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>7535e659-af87-485d-b94d-cb398b82610b</rd:ReportID>
<PageFooter>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.125in</Top>
<rd:DefaultName>textbox2</rd:DefaultName>
<Width>2in</Width>
<CanGrow>true</CanGrow>
<Value>="Page " & Globals!PageNumber</Value>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.375in</Height>
</PageFooter>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||Kevin,
Did you get your last idea implemented in code? If so, would you share it
with me?
Thanks,
Ming
"Kevin Wilson" wrote:
> Thanks for your time on this Bruce. The multi coloumn report works fine but it isn't really appropriate in this case as I need to view the data rendered in HTML.
> My last idea is to change the the stored proc so each row is identified with a unique number. Create two tables each with a filter - table1 returns odd rows Fields!ID_NUM MOD 2 = 1 and table 2 returns even rows.
> Cheers
> Kevin
> "Bruce Johnson [MSFT]" wrote:
> > Could you use a multi-column report and limt the number of rows in each
> > column?
> > The major restriction is that you must use a "paged" rendered (PDF, Print
> > Preview) to see the multiple columns. Following your original posting is a
> > sample RDL that shows how this is done.
> > Note the table group expression.
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> > news:8512662A-BEDB-4194-8072-03CFFC7E8736@.microsoft.com...
> > > Hi,
> > > Any ideas on how to split data from a row set over two columns in a list
> > box or table?
> > > For example I have a data set with 10 rows and I want to display 5 rows in
> > one column and the other 5 in the second column.
> > > I tried creating two tables and filtering the first table to only show the
> > even rows and the second to show odd rows. However the RowNumber(Nothing)
> > function is not allowed in filters - 'RowNumber cannot be used in filters'
> > > Thanks
> > > Kevin
> >
> > --
> >
> > Sample Multi-Column Report - 5 records per column
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> > tion"
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <PageHeader>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontFamily>Franklin Gothic Medium</FontFamily>
> > <FontSize>12pt</FontSize>
> > <TextAlign>Center</TextAlign>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <Top>0.11458in</Top>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <Height>0.25in</Height>
> > <Width>3in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>Sample Multiple Column Report</Value>
> > <Left>1.5in</Left>
> > </Textbox>
> > </ReportItems>
> > <PrintOnLastPage>true</PrintOnLastPage>
> > <PrintOnFirstPage>true</PrintOnFirstPage>
> > <Style />
> > <Height>0.5in</Height>
> > </PageHeader>
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>11pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>3</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>Company Info</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > <RepeatOnNewPage>true</RepeatOnNewPage>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="CompanyName">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>CompanyName</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!CompanyName.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="City">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>City</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!City.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="Country">
> > <rd:DefaultName>Country</rd:DefaultName>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Country.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>Northwind</DataSetName>
> > <TableGroups>
> > <TableGroup>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> >
> > <GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
> > >
> > </GroupExpressions>
> > <PageBreakAtEnd>true</PageBreakAtEnd>
> > </Grouping>
> > </TableGroup>
> > </TableGroups>
> > <TableColumns>
> > <TableColumn>
> > <Width>2.75in</Width>
> > </TableColumn>
> > </TableColumns>
> > </Table>
> > </ReportItems>
> > <Style />
> > <Height>1.5in</Height>
> > <Columns>2</Columns>
> > </Body>
> > <TopMargin>1in</TopMargin>
> > <DataSources>
> > <DataSource Name="Northwind">
> >
> > <rd:DataSourceID>7bec244d-832e-4036-9143-257dd0f8dcaa</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>data source=localhost;initial
> > catalog=Northwind</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>2.75in</Width>
> > <DataSets>
> > <DataSet Name="Northwind">
> > <Fields>
> > <Field Name="CompanyName">
> > <DataField>CompanyName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="City">
> > <DataField>City</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="Country">
> > <DataField>Country</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>Northwind</DataSourceName>
> > <CommandText>SELECT CompanyName, City, Country
> > FROM Customers</CommandText>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>1in</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>7535e659-af87-485d-b94d-cb398b82610b</rd:ReportID>
> > <PageFooter>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <Top>0.125in</Top>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <Width>2in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>="Page " & Globals!PageNumber</Value>
> > </Textbox>
> > </ReportItems>
> > <PrintOnLastPage>true</PrintOnLastPage>
> > <PrintOnFirstPage>true</PrintOnFirstPage>
> > <Style />
> > <Height>0.375in</Height>
> > </PageFooter>
> > <BottomMargin>1in</BottomMargin>
> > <Language>en-US</Language>
> > </Report>
> >
> >
> >

Friday, March 30, 2012

Load Excel Data using DTS NULL error

Load Excel Data using DTS NULL error

Load Excel Data using DTS NULL error

Friday, March 23, 2012

Listing words with no of occurances in sql server

Wednesday, March 21, 2012

listing columns in view and stored procedures

Listing all tables and their columns?

Monday, March 12, 2012

List only the visible columns in a view?

Wednesday, March 7, 2012

list of columns in a table

List of columns from tables across databases.

list of columns

list of columns

Friday, February 24, 2012

List Box - Multiple Columns

List availables tables

Blog Archive

About Me