Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Friday, March 30, 2012
Information_Schema query
Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement = 'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
--
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>sql
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement = 'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
--
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>sql
Information_Schema query
Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
Simon
Only with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan
|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon
|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegro ups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
Simon
Only with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan
|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon
|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegro ups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>
Friday, March 23, 2012
indices and replication
Will leaving these indexes there cause any
>problems?
No.
I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
Correct
>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
Dont know.
2 outta 3 aint bad. ;-)
>--Original Message--
>first: forgive me, is it indices or indexes?
>I had previously asked about adding indexes to tables
that are a part of
>merge replication (sql 2000 servers). Paul Ibison
reccomended checking out
>sp_addscriptexec. I honestly have not done that yet as I
was just doing a
>little initial digging on the subject at that point. I
have a couple other
>questions surrounding this issue:
>1) I had someone add some indexes to replicated tables on
the subcriber end
>of a merge replication scheme by using the usual
Enterprise Manager GUI
>method. They did not replicate to the publisher. I assume
that is normal. I
>actually did not think EM would have let the change be
done since it was
>done on a replication article. Will leaving these indexes
there cause any
>problems? I don't actually need them on the publisher end
right now anyway.
>2) I see that by default indexes are included when a new
snapshot is applied
>to a subscriber. So, if re-applying a new snapshot is a
doable solution, I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
>any info is greatly appreciated. Thanks!
>
>.
>
2 out of 3 ain't bad at all... Thanks! ;)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1b6801c4a19e$1ef9d250$a401280a@.phx.gbl...[vbcol=seagreen]
> Will leaving these indexes there cause any
> No.
> I
> method and just
> subscribers... Correct?
> Correct
>
> a published article
> subscribers. Just like
>
> Dont know.
> 2 outta 3 aint bad. ;-)
>
> that are a part of
> reccomended checking out
> was just doing a
> have a couple other
> the subcriber end
> Enterprise Manager GUI
> that is normal. I
> done since it was
> there cause any
> right now anyway.
> snapshot is applied
> doable solution, I
> method and just
> subscribers... Correct?
> a published article
> subscribers. Just like
|||I agree with Chris and (3) yes you can use sp_addscriptexec to send over the
create index statements - or use EM, or scripts etc.
The reason sp_addscriptexec is particularly useful is if you have a lot of
subscribers or if your subscribers are pull subscriptions - you can be sure
everyone gets the change. Be sure to try the script on the publisher first.
A recent poster found that his distribution agent failed due to an incorrect
script. If this happens, then you'll need to edit the script in the repldata
share.
BTW for (2) This is usually a lot less work than recreating a snapshot and
synchronizing.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uYFfuGaoEHA.2864@.TK2MSFTNGP12.phx.gbl...
> I agree with Chris and (3) yes you can use sp_addscriptexec to send over
the
> create index statements - or use EM, or scripts etc.
> The reason sp_addscriptexec is particularly useful is if you have a lot of
> subscribers or if your subscribers are pull subscriptions - you can be
sure
> everyone gets the change. Be sure to try the script on the publisher
first.
> A recent poster found that his distribution agent failed due to an
incorrect
> script. If this happens, then you'll need to edit the script in the
repldata
> share.
> BTW for (2) This is usually a lot less work than recreating a snapshot and
> synchronizing.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>problems?
No.
I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
Correct
>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
Dont know.
2 outta 3 aint bad. ;-)
>--Original Message--
>first: forgive me, is it indices or indexes?
>I had previously asked about adding indexes to tables
that are a part of
>merge replication (sql 2000 servers). Paul Ibison
reccomended checking out
>sp_addscriptexec. I honestly have not done that yet as I
was just doing a
>little initial digging on the subject at that point. I
have a couple other
>questions surrounding this issue:
>1) I had someone add some indexes to replicated tables on
the subcriber end
>of a merge replication scheme by using the usual
Enterprise Manager GUI
>method. They did not replicate to the publisher. I assume
that is normal. I
>actually did not think EM would have let the change be
done since it was
>done on a replication article. Will leaving these indexes
there cause any
>problems? I don't actually need them on the publisher end
right now anyway.
>2) I see that by default indexes are included when a new
snapshot is applied
>to a subscriber. So, if re-applying a new snapshot is a
doable solution, I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
>any info is greatly appreciated. Thanks!
>
>.
>
2 out of 3 ain't bad at all... Thanks! ;)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1b6801c4a19e$1ef9d250$a401280a@.phx.gbl...[vbcol=seagreen]
> Will leaving these indexes there cause any
> No.
> I
> method and just
> subscribers... Correct?
> Correct
>
> a published article
> subscribers. Just like
>
> Dont know.
> 2 outta 3 aint bad. ;-)
>
> that are a part of
> reccomended checking out
> was just doing a
> have a couple other
> the subcriber end
> Enterprise Manager GUI
> that is normal. I
> done since it was
> there cause any
> right now anyway.
> snapshot is applied
> doable solution, I
> method and just
> subscribers... Correct?
> a published article
> subscribers. Just like
|||I agree with Chris and (3) yes you can use sp_addscriptexec to send over the
create index statements - or use EM, or scripts etc.
The reason sp_addscriptexec is particularly useful is if you have a lot of
subscribers or if your subscribers are pull subscriptions - you can be sure
everyone gets the change. Be sure to try the script on the publisher first.
A recent poster found that his distribution agent failed due to an incorrect
script. If this happens, then you'll need to edit the script in the repldata
share.
BTW for (2) This is usually a lot less work than recreating a snapshot and
synchronizing.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uYFfuGaoEHA.2864@.TK2MSFTNGP12.phx.gbl...
> I agree with Chris and (3) yes you can use sp_addscriptexec to send over
the
> create index statements - or use EM, or scripts etc.
> The reason sp_addscriptexec is particularly useful is if you have a lot of
> subscribers or if your subscribers are pull subscriptions - you can be
sure
> everyone gets the change. Be sure to try the script on the publisher
first.
> A recent poster found that his distribution agent failed due to an
incorrect
> script. If this happens, then you'll need to edit the script in the
repldata
> share.
> BTW for (2) This is usually a lot less work than recreating a snapshot and
> synchronizing.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Subscribe to:
Comments (Atom)