how to script security permissions?

I have a very large table that is refreshed periodically. Since it's
so big, I do a 'drop table', 'create table', 'create index' then a bulk
load. It's much faster than doing a 'delete from'. I also do a
'shrinkdb' as part of this process.

The problem, however, is that the user permissions are also dropped in
this process. So, how can I script the user permissions? For example,
how do I give 'MyUser' select access to 'MyTable' in 'MyDB'? Many
thanks!!

Eben Yong
yonglove@.yahoo.comGRANT SELECT ON MyTable TO MyUser|||Eben (yonglove@.yahoo.com) writes:
> I have a very large table that is refreshed periodically. Since it's
> so big, I do a 'drop table', 'create table', 'create index' then a bulk
> load. It's much faster than doing a 'delete from'. I also do a
> 'shrinkdb' as part of this process.

So why not do a TRUNCATE TABLE instead? This is a minimally logged
operation, and you maintain indexes, permissions etc.

> The problem, however, is that the user permissions are also dropped in
> this process. So, how can I script the user permissions? For example,
> how do I give 'MyUser' select access to 'MyTable' in 'MyDB'? Many
> thanks!!

I guess you can do this with DMO, if you want to do this programmatically.
However, I have not used DMO myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Eben (yonglove@.yahoo.com) writes:
> > I have a very large table that is refreshed periodically. Since it's
> > so big, I do a 'drop table', 'create table', 'create index' then a bulk
> > load. It's much faster than doing a 'delete from'. I also do a
> > 'shrinkdb' as part of this process.
> So why not do a TRUNCATE TABLE instead? This is a minimally logged
> operation, and you maintain indexes, permissions etc.
Although keeping indexes is sometimes a good thing, it's also sometimes
beneficial to drop all indexes before the truncate and only add them
back when all the data loading is complete.

Of course, it depends on how the data loading is being managed (in my
case, I happen to be loading 3 1/2 million records from a non-R DBMS,
and having to do it one row at a time - I'd rather not have the indexes
rebuilt for every insert)

Damien|||Thank you, everyone. I did not know about the TRUNCATE TABLE option.
But accomplishing this objective using DROP TABLE, CREATE TABLE, and so
on, has required that I learn many other SQL Server methods, so it's
good for me. I spent more time developing the solution but SQL Server
doesn't care one way or the other and both methods still get the job
done in the same amount of time. So, once again, thanks everyone for
your input!

How to script index in 2005

sql 2005
right click on table to script - create script does not include indexes, or
perms. how to include?
any help appreciated
thanks
chris
Hi,
use the script wizard and enable the option "Include Script Indexes".
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Jens
> use the script wizard and enable the option "Include Script Indexes".
I thought it will be able in SP2 comming soon,isn't it?
If it isn't can yoi point out?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168553540.198986.94110@.77g2000hsv.googlegrou ps.com...
> Hi,
> use the script wizard and enable the option "Include Script Indexes".
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

How to script index in 2005

sql 2005
right click on table to script - create script does not include indexes, or
perms. how to include?
any help appreciated
thanks
chrisHi,
use the script wizard and enable the option "Include Script Indexes".
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Jens
> use the script wizard and enable the option "Include Script Indexes".
I thought it will be able in SP2 comming soon,isn't it?
If it isn't can yoi point out?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168553540.198986.94110@.77g2000hsv.googlegroups.com...
> Hi,
> use the script wizard and enable the option "Include Script Indexes".
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableName
If you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>
|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
they
>
|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>
|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableNameIf you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
they[vbcol=seagreen]
>|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableNameIf you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> > Using 2005
> >
> > I want to generate a list of all tables that have indexes, so I can
> > reindex
> > them. I don't want to bother doing a reindex on a table that has no
> > indexes. I thought it would be easy enough to pull this data from
> > sys.indexes (or something like that), but it is quirky. The following
> > query
> > will return some tables, showing them as having 1 index, when really
they
> > have NO indexes. But yet other tables result with having 1 index, when
> > that
> > is correct. And this query also returns dtproperties, which is not one
> > of
> > my user tables, so that also makes this query incorrect.
> >
> > This is what I'm using, and maybe someone can help fine tune it so it is
> > 100% accurate... Try this against any of your DBs and you'll see it is
> > not
> > quite accurate even though mostly accurate...
> >
> > Select object_name(object_id) as TableName, count(object_id) as
> > NumIndexes,
> > object_id
> > from sys.indexes
> > where object_id>1000 -- eliminate some system table junk below 1000
> > group by object_id
> > order by TableName
> >
> >
>|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> If you just want a list of tables that contain indexes, you can do this:
>> SELECT name
>> FROM sys.tables
>> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
>> ORDER BY name;
>> If you want the tables and the index names, try this variation:
>> SELECT
>> t.name, i.name, i.type_desc
>> FROM sys.indexes AS i INNER JOIN
>> sys.tables AS t ON t.object_id = i.object_id
>> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
>> ORDER BY t.name
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
>> > Using 2005
>> >
>> > I want to generate a list of all tables that have indexes, so I can
>> > reindex
>> > them. I don't want to bother doing a reindex on a table that has no
>> > indexes. I thought it would be easy enough to pull this data from
>> > sys.indexes (or something like that), but it is quirky. The following
>> > query
>> > will return some tables, showing them as having 1 index, when really
> they
>> > have NO indexes. But yet other tables result with having 1 index, when
>> > that
>> > is correct. And this query also returns dtproperties, which is not
>> > one
>> > of
>> > my user tables, so that also makes this query incorrect.
>> >
>> > This is what I'm using, and maybe someone can help fine tune it so it
>> > is
>> > 100% accurate... Try this against any of your DBs and you'll see it is
>> > not
>> > quite accurate even though mostly accurate...
>> >
>> > Select object_name(object_id) as TableName, count(object_id) as
>> > NumIndexes,
>> > object_id
>> > from sys.indexes
>> > where object_id>1000 -- eliminate some system table junk below 1000
>> > group by object_id
>> > order by TableName
>> >
>> >
>>
>|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> If you just want a list of tables that contain indexes, you can do this:
>> SELECT name
>> FROM sys.tables
>> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
>> ORDER BY name;
>> If you want the tables and the index names, try this variation:
>> SELECT
>> t.name, i.name, i.type_desc
>> FROM sys.indexes AS i INNER JOIN
>> sys.tables AS t ON t.object_id = i.object_id
>> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
>> ORDER BY t.name
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
>> > Using 2005
>> >
>> > I want to generate a list of all tables that have indexes, so I can
>> > reindex
>> > them. I don't want to bother doing a reindex on a table that has no
>> > indexes. I thought it would be easy enough to pull this data from
>> > sys.indexes (or something like that), but it is quirky. The following
>> > query
>> > will return some tables, showing them as having 1 index, when really
> they
>> > have NO indexes. But yet other tables result with having 1 index, when
>> > that
>> > is correct. And this query also returns dtproperties, which is not
>> > one
>> > of
>> > my user tables, so that also makes this query incorrect.
>> >
>> > This is what I'm using, and maybe someone can help fine tune it so it
>> > is
>> > 100% accurate... Try this against any of your DBs and you'll see it is
>> > not
>> > quite accurate even though mostly accurate...
>> >
>> > Select object_name(object_id) as TableName, count(object_id) as
>> > NumIndexes,
>> > object_id
>> > from sys.indexes
>> > where object_id>1000 -- eliminate some system table junk below 1000
>> > group by object_id
>> > order by TableName
>> >
>> >
>>
>

How to Retrieve Next Value from Field?

Hi All,

I have a table with a column called SRGTE_KEY_1 which is also referenced as an index with an Index name of EMDET_i0. The Data Type is Varbinary.

I have a trigger when activated inserts data into this table. However my problem is that I need to determine what is the next available Varbinary value in the SRGTE_KEY_1 column so I can use this value with my Insert otherwise the trigger will Fail if I dont specify a Unique Value.

What SQL statement/s can I write to be able to obtain the next Unique Value from this Column.

A Step by Step procedure would be great as well as syntax. Thanks for your help.

Regards
AnthonyWhat do you mean by the next available value?
If you mean treat it like an integer and add 1 then maybe it shouldn't be a varbinary.
Or do you have a tabe which gives the available values?|||By the next available value I mean any value that is not being used(thats what I mean by next available) so duplication wont occur. I'm not sure how to obtain this as its varbinary. I wouldn't have a problem if it was a numeric value, but I don't know about this data type.