finding columns

In a specific database. I would like to check to see if a particular column
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and Query Analyzer for example. You can do it programatically via ADO.OpenSchema(), sp_help, or DMO... Of course querying the system tables is always an option -- something like (this doesn't narrow to the database level)
select so.name as 'Table', sc.Name as 'Column
from syscolumns s
join sysobjects so on so.id = sc.i
wher
xo.xtype = 'u
--an
--sc.name like '%column_name_to_find%
group by so.name,sc.nam
...the usual caveats apply (MS does not recommend using the system tables, etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > In a specific database. I would like to check to see if a particular
> column
> > exists in all my tables i.e user created tables. I know I have to use
the
> > sysobjects and syscolumns table. But I am sorta at loss to find out how
> they
> > are related.
> >
> > Please Help
> >
> > VJ
> >
> >
>

finding columns

In a specific database. I would like to check to see if a particular column
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and
Query Analyzer for example. You can do it programatically via ADO.OpenSche
ma(), sp_help, or DMO... Of course querying the system tables is always an
option -- something like (t
his doesn't narrow to the database level):
select so.name as 'Table', sc.Name as 'Column'
from syscolumns sc
join sysobjects so on so.id = sc.id
where
xo.xtype = 'u'
--and
--sc.name like '%column_name_to_find%'
group by so.name,sc.name
...the usual caveats apply (MS does not recommend using the system tables,
etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> column
the
> they
>

Finding all views with a specific name, in all databases

Hi,

We are migrating to SQL Server 2005 from 2000 and we have a view created in many of our databases. That view must be changed after the migration because it uses system tables and does not return the correct information in SQL Server 2005.

To do that, I want to create an SSIS-package that loops through all the databases on a particular server. If that view exists in the database, I want to run a script that change the view.

What I try to do:

Set up a For Each Loop container with Foreach SMO Enumerator

Set the connection to my server

Set the Enumerate property to "SMOEnumObj[@.Name='Databases']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place Index 0 in Variable User::dbName

In the For Each Loop, place a script task to msgbox the value of User::dbName

This all works good. The problem comes when I try to nest the For Each Loop

Inside the "For Each Database Loop", place a new For Each Loop container with Foreach SMO Enumerator

I connect the Msgbox Script Task with the new For each loop

Use the same connection

Set the EnumURN property in the Expression Builder to "Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place index 0 in Variable User::tabName

In the For Each Loop, place a script task to msgbox the value of User::tabName

When I try to run the package now, it does not at all.

In my Progress tab I see that Validation has started and completed for each task, but I see now errors. Nothing has run.

Test 1: I change the DelayValidation for the Inner Loop to True

Now the package runs, but I never get to the script task in the inner loop.

Question 1: What's the problem?

Question 2: Is there another way to do this?

Regards

Magnus

Have you tried using a second connection? It seems like the above should work for you, but maybe there's an issue with the SMO Enumerator that requires a different connection for the inner loop.|||

Hi Sean, and thanks for your response.

I tried to use a second connection, but I still get the same result.

/Magnus

|||Forget the SMO enumerator in your Foreach loop.

Do an Execute SQL Task and use something like:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'VIEWNAME%'

Then set the resultset to full result set and create an object type var to hold the results. Then loop thru the results using the ForEach ADO.

See this example. I found it very helpful.

http://www.cubido.at/Blog/tabid/176/EntryID/59/Default.aspx|||

Hi Tom, I tried your suggestion and it worked alright. Thanks!

Because I only expect a single row, I used a Single Row instead of Full ResultSet and used a SQL like

SELECT ISNULL(Max(TABLE_NAME), '') AS TableName FROM [databasename].INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewName'

I set the SQLStatement with an expression since I needed to change the databasename for each iteration. I placed the field TableName in a variable. Before I move on to the next step, where I want to alter the view, I use a Constraint and Expression constraint and make sure that the variable is not empty.

Then I create an Execute SQL task to alter the view. I use the same connection as in the above task and use an expression to create the SQLStatement to something like:

USE [databasename]
GO

ALTER VIEW [dbo].[viewName]
AS
SELECT *
FROM theTable

/Magnus

|||

I had the same problem except I wasn't using SMO Enumeration for the outter loop. You can't just use set the EnumURN property in the Expression Builder to...
"Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Your missing two parts. You have to set the Expression to...
"RuntimeServer[@.Name='<the name of your SMO Connection in the Connection Manager>']/Server[@.Name='<the server name>']/Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Hope this at least helps someone down the road. It took me 4 hours to figure it out! Either I'm retarded or it's not explained well.

sql

Finding all views with a specific name, in all databases

Hi,

We are migrating to SQL Server 2005 from 2000 and we have a view created in many of our databases. That view must be changed after the migration because it uses system tables and does not return the correct information in SQL Server 2005.

To do that, I want to create an SSIS-package that loops through all the databases on a particular server. If that view exists in the database, I want to run a script that change the view.

What I try to do:

Set up a For Each Loop container with Foreach SMO Enumerator

Set the connection to my server

Set the Enumerate property to "SMOEnumObj[@.Name='Databases']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place Index 0 in Variable User::dbName

In the For Each Loop, place a script task to msgbox the value of User::dbName

This all works good. The problem comes when I try to nest the For Each Loop

Inside the "For Each Database Loop", place a new For Each Loop container with Foreach SMO Enumerator

I connect the Msgbox Script Task with the new For each loop

Use the same connection

Set the EnumURN property in the Expression Builder to "Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place index 0 in Variable User::tabName

In the For Each Loop, place a script task to msgbox the value of User::tabName

When I try to run the package now, it does not at all.

In my Progress tab I see that Validation has started and completed for each task, but I see now errors. Nothing has run.

Test 1: I change the DelayValidation for the Inner Loop to True

Now the package runs, but I never get to the script task in the inner loop.

Question 1: What's the problem?

Question 2: Is there another way to do this?

Regards

Magnus

Have you tried using a second connection? It seems like the above should work for you, but maybe there's an issue with the SMO Enumerator that requires a different connection for the inner loop.|||

Hi Sean, and thanks for your response.

I tried to use a second connection, but I still get the same result.

/Magnus

|||Forget the SMO enumerator in your Foreach loop.

Do an Execute SQL Task and use something like:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'VIEWNAME%'

Then set the resultset to full result set and create an object type var to hold the results. Then loop thru the results using the ForEach ADO.

See this example. I found it very helpful.

http://www.cubido.at/Blog/tabid/176/EntryID/59/Default.aspx|||

Hi Tom, I tried your suggestion and it worked alright. Thanks!

Because I only expect a single row, I used a Single Row instead of Full ResultSet and used a SQL like

SELECT ISNULL(Max(TABLE_NAME), '') AS TableName FROM [databasename].INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewName'

I set the SQLStatement with an expression since I needed to change the databasename for each iteration. I placed the field TableName in a variable. Before I move on to the next step, where I want to alter the view, I use a Constraint and Expression constraint and make sure that the variable is not empty.

Then I create an Execute SQL task to alter the view. I use the same connection as in the above task and use an expression to create the SQLStatement to something like:

USE [databasename]
GO

ALTER VIEW [dbo].[viewName]
AS
SELECT *
FROM theTable

/Magnus

|||

I had the same problem except I wasn't using SMO Enumeration for the outter loop. You can't just use set the EnumURN property in the Expression Builder to...
"Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Your missing two parts. You have to set the Expression to...
"RuntimeServer[@.Name='<the name of your SMO Connection in the Connection Manager>']/Server[@.Name='<the server name>']/Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Hope this at least helps someone down the road. It took me 4 hours to figure it out! Either I'm retarded or it's not explained well.

Filters for dataset from Parameter - How to ignore if the parameter is NULL ?

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.

I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :

Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.

Thanks

One way would be to use this in your stored procedure or query, with coalesce...

eg. AND COALESCE(@.RegionCode,[RegionCode]) = [RegionCode]

That way you're not bringing the data into the report and then filtering it out, you're filtering at the source.

|||

No I don't want to do that,

The idea is the get ALL the data in a snapshot nightly then apply filters on the snapshot when we display to the users.

The result is that the DB will not get hammered everytime a report is run but only once at night.

So the coalesce will not work.

|||

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

|||

Andrew - T4G wrote:

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

Thanks, I'll try something like that.

Filters - A good way to limit data at subscribers?

We use merge replication where data created at a control centre, then
modified in a filtered publication at the subscribers. We currently have
about 100 subscribers.
Publication is filter is WHERE LocationRef=XXX
We need to retain data at the control centre but not at subscribers, so does
it make sense to alter the filter to somthing like WHERE LocationRef=XXX AND
VisitDate>YYYY to achieve this, thereby reducing the data stored at each
subscriber?
Tony Toker
Data Identic Ltd.
This is not the correct way to do this.
What will happen is that data which is modified at the publisher or
subscriber will be merged if it meets the filtering criteria. However as the
data ages at the publisher or subscriber it will remain there, unless there
is a job which deletes/archives these rows so the merge replication process
will identify them as changed and then delete them from the publisher and
susbcriber. You would probably want to initiate the delete on the subscriber
to remove them there, but retail them on the publisher.
"Tony Toker" <xyzzy@.identic.co.uk> wrote in message
news:cqbl9o$dkv$1$8300dec7@.news.demon.co.uk...
> We use merge replication where data created at a control centre, then
> modified in a filtered publication at the subscribers. We currently have
> about 100 subscribers.
> Publication is filter is WHERE LocationRef=XXX
> We need to retain data at the control centre but not at subscribers, so
does
> it make sense to alter the filter to somthing like WHERE LocationRef=XXX
AND
> VisitDate>YYYY to achieve this, thereby reducing the data stored at each
> subscriber?
> Tony Toker
> Data Identic Ltd.
>