declare @columns table (id int identity(0, 1), tablename sysname, columnname sysname)

insert		@columns
		(
			tablename,
			columnname
		)
select		isc.table_name,
		isc.column_name
from		information_schema.columns isc
inner join	information_schema.tables ist on ist.table_name = isc.table_name
where		isc.data_type in ('datetime', 'smalldatetime')
		and ist.table_type = 'base table'

declare	@id int,
	@sql varchar(2000),
	@tablename sysname,
	@columnname sysname

select	@id = max(id)
from	@columns

while @id >= 0
	begin
		select	@tablename = quotename(tablename),
			@columnname = quotename(columnname)
		from	@columns
		where	id = @id

		SELECT @SQL = 'select DISTINCT(' + @columnname + ') from ' + @tablename + ' order by ' + @columnname + ' ASC',
			@id = @id - 1

		exec	(@sql)
	end

quote:

---

way to access all the dbs and their tables inside

---

quote:

---

Originally posted by raysefo

Hi,

here is the code segment below;
...
DECLARE find_dates CURSOR FOR
SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC'
EXEC (@SQL)

but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help.

thanks

---