script to generate db scham of all the databases in a sql server instance

• chandrika5

  SSCrazy

  Points: 2734

  More actions

  February 22, 2007 at 9:34 am

  #172391

  I am running sqlserver 2000 on win2k+3.

  Per microsoft DST KB:

  http://support.microsoft.com/?kbid=931975&SD=tech

  the getDate s/be getUTCdate.

  Now I have serveral instance with many databases per instance. It might take a long time to get any default date time set to current.

  Is there a way I can generate a db schema of all the tables with defaults for an instance?

  any help is appreciated.

  thanks,

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 22, 2007 at 2:01 pm

  #691253

  I think this is what you are looking for: this is for a single database, you'd need to wrap this up for the sp_msForEachdb proc for every location on every db on a server.

  it will list the tablename and the constraint name of any defaults that have the words getdate in it's definition:

  select object_name(parent_obj) As TableName,name as DefConName from sysobjects

  inner join syscomments on sysobjects.id=syscomments.id

  where xtype='D' --default constriant

  and syscomments.text like '%getdate%'

  sample results:

  TABLENAME	DEFCONNAME
  COMPPROJECT	DF__COMPPROJE__EFFDT__60DEC83E
  GMPVERS	DF__GMPVERS__CREATED__0961BAE4
  GMPTRANS	DF__GMPTRANS__CREATE__0B4A0356
  GMFLIMPLOG	DF__GMFLIMPLO__IMPOR__0C0786B7
  GMPTDET	DF__GMPTDET__CREATED__0D324BC8

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 22, 2007 at 2:20 pm

  #691255

  improved to include the column as well:

  select

  object_name(parent_obj) As TableName,

  syscolumns.name as Columnname ,

  sysobjects.name as DefConName from sysobjects

  inner join syscomments on sysobjects.id=syscomments.id

  inner join syscolumns on sysobjects.parent_obj=syscolumns.id and sysobjects.info =syscolumns.colid

  where sysobjects.xtype='D' --default constriant

  and syscomments.text like '%getdate%'

  COMPPROJECT	EFFDT	DF__COMPPROJE__EFFDT__60DEC83E
  GMPVERS	CREATEDDT	DF__GMPVERS__CREATED__0961BAE4
  GMPTRANS	CREATEDDT	DF__GMPTRANS__CREATE__0B4A0356

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• chandrika5

  SSCrazy

  Points: 2734

  More actions

  February 22, 2007 at 3:29 pm

  #691271

  thanks Lowell, I figured this out.

  Coming back, the KB is confusing. It says use getUTFdate() in TSQL stmts  instead go getDate()  for dateDiff/dateAdd functions. But does not say the same while setting up the defaults!!!. Not sure if I should change the defaults on the tables ...

  any input on this ... Should I change or leave AsIs...