Cursor to get all tables with identity

To help VTBOS we needed to get all tables with Identity. They wanted replication on most of their databases and to get it to work we first need to make sure that every identity is marked "not for replication". This is one way to get information about every table in every database. Can be altered to get other information from every database.

use master
go
declare @cdatabasename varchar(128)
declare @string varchar(500)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
set @string = '' + @@servername +''
fetch next from Nonsysdatabases
into @cdatabasename
while @@fetch_status = 0
begin
set @string = 'use ' +@cdatabasename+ ' select table_name ,''' + @cdatabasename +''' as DatabaseName
from information_schema.tables
where objectproperty(object_id(table_name), ''isusertable'') = 1
and objectproperty(object_id(table_name), ''ismsshipped'') = 0
and ident_current(table_name) is not null'
exec (@string)
fetch next from Nonsysdatabases
into @cdatabasename
end
close Nonsysdatabases
deallocate Nonsysdatabases


this code can be used to get all tables with identity and all tables with identity set to not for replication.