Remove All Identity Tables
-
I want To Remove All Identity Tables From My Database(Attention:i Have Some Table That dont Have Identity).How Can I Do That???????????????
-
This query will identifiy and generate the DROP TABLE statements for you. Beware that it does not analyze foreign keys and does not drop tables in an order that will prevent errors due to foreign key constraint.
Use it with care. I take no responsibility for any errors in the query.
select 'drop table ' + quotename(s.name) + '.' + quotename(t.name)from sys.tables t
inner join sys.schemas s on t.schema_id=s.schema_id
where t.is_ms_shipped=0
and exists(select * from sys.columns c where c.is_identity=1 and c.object_id=t.object_id)
order by t.name
-
Here is the query to get the list of all "User" tables have identity column:
SELECT DISTINCT Object_name(t.object_id)FROM sys.all_columns c
INNER JOIN sys.tables t
ON t.object_id = c.object_id
WHERE is_identity = 1
AND t.type = 'U'
Create a drop script out of the list generated by the query. Make sure you drop all the FK references to these tables before dropping them. Hope this helps.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
NO friend
I want To Set Identity Off for All Tables not drop table.
-
vahid.arr (10/9/2012)
NO friendI want To Set Identity Off for All Tables not drop table.
Here you go:
SELECT DISTINCT 'SET IDENTITY_INSERT '+ Object_name(t.object_id) + ' OFF' + Char(10)
+ 'GO' + Char(10)
FROM sys.all_columns c
INNER JOIN sys.tables t
ON t.object_id = c.object_id
WHERE is_identity = 1
AND t.type = 'U'
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
When you say "Set Identity Off", do you mean that you don't want the column to be be Identity anymore?
-
Lokesh Vij (10/9/2012)Here you go:
Won't work. Only one table at the time can have IDENTITY_INSERT OFF
-
Nils Gustav Stråbø (10/9/2012)
Lokesh Vij (10/9/2012)Here you go:
Won't work. Only one table at the time can have IDENTITY_INSERT OFF
It will certainly work. Execute the query - this will generate a script. Execute that script and check the results.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Nils Gustav Stråbø (10/9/2012)
When you say "Set Identity Off", do you mean that you don't want the column to be be Identity anymore?SELECT DISTINCT 'SET IDENTITY_INSERT '
+ Object_name(t.object_id) + ' Off' + Char(10)
+ 'GO' + Char(10)
FROM sys.all_columns c
INNER JOIN sys.tables t
ON t.object_id = c.object_id
WHERE is_identity = 1
AND t.type = 'U'
this query give all of them.How can I perform It One By one???????
also I have 80 Table I cant Set OFf tehm one by one
-
Lokesh Vij (10/9/2012)
Nils Gustav Stråbø (10/9/2012)
Lokesh Vij (10/9/2012)Here you go:
Won't work. Only one table at the time can have IDENTITY_INSERT OFF
It will certainly work. Execute the query - this will generate a script. Execute that script and check the results.
this give me the last Script.what is the problem???????
Declare @Query varchar(max)
set @Query=''
SELECT DISTINCT @query+= 'SET IDENTITY_INSERT '
+ Object_name(t.object_id) + ' Off' + Char(10)
+ 'GO' + Char(10)
FROM sys.all_columns c
INNER JOIN sys.tables t
ON t.object_id = c.object_id
WHERE is_identity = 1
AND t.type = 'U'
select @Query
-
Nils Gustav Stråbø (10/9/2012)
Lokesh Vij (10/9/2012)Here you go:
Won't work. Only one table at the time can have IDENTITY_INSERT OFF
Only one table at the time can have IDENTITY_INSERT ON.
I don't think setting it to off for all tables has any effect, as they're already set to that?
You can't remove the identity property from a column - you'll need to drop the column.
-
Nils Gustav Stråbø (10/9/2012)
I take my words back. You are correct... Identity_insert will work only for one table at a time 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Gazareth (10/9/2012)
Nils Gustav Stråbø (10/9/2012)
Lokesh Vij (10/9/2012)Here you go:
Won't work. Only one table at the time can have IDENTITY_INSERT OFF
Only one table at the time can have IDENTITY_INSERT ON.
I don't think setting it to off for all tables has any effect, as they're already set to that?
You can't remove the identity property from a column - you'll need to drop the column.
Thanks Gazareth for correcting me 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
vahid.arr (10/9/2012)
NO friendI want To Set Identity Off for All Tables not drop table.
You can - but sequentially, not all at the same time.
Why do you want to do this?
How long for?
What will you do to the tables when you have "Set Identity Off"?
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Lokesh Vij (10/9/2012)
Gazareth (10/9/2012)
Nils Gustav Stråbø (10/9/2012)
Lokesh Vij (10/9/2012)Here you go:
Won't work. Only one table at the time can have IDENTITY_INSERT OFF
Only one table at the time can have IDENTITY_INSERT ON.
I don't think setting it to off for all tables has any effect, as they're already set to that?
You can't remove the identity property from a column - you'll need to drop the column.
Thanks Gazareth for correcting me 🙂
No problem. Think we're all a little confused as to what the OP is trying to do 🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply