Date Created: 2009-11-07

SUMMARY: this is sql system table queries which tell me date modifications and the date tables were created.  SQL 2005 does lack the loggin part of when a column has been modifed in a sql table.

HERE ARE DIFFERENT SQL Queries I have used in the past

SELECT DISTINCT TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME LIKE '%MSmerge%'

ORDER BY TABLE_NAME

-------------------------------------------------------------------------------

-- SQL SERVER - 2005 - Find Stored Procedure Create Date and Modified Date

-- http://blog.sqlauthority.com/2007/08/10/sql-server-2005-find-stored-procedure-create-date-and-modified-date/

-------------------------------------------------------------------------------

-- show the modify date of the stored procedures

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

-- show the modify date of the tables

-------------------------------------------------------------------------------

----------------------------------------------------------

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE lower(COLUMN_NAME) like '%sn%'

ORDER BY TABLE_NAME

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE --lower(COLUMN_NAME) like '%companyid%' and

ORDER BY TABLE_NAME,

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE='timestamp'

ORDER BY TABLE_NAME

SELECT distinct * --TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE='BASE TABLE'

and TABLE_NAME not like 'dtproperties%'

and TABLE_NAME not like 'hop_trace%'

and TABLE_NAME not like 'MSys%'

and TABLE_NAME not like 'sysDiagrams%'

-- GET ALL USER-DEFINED TABLE FROM MyDBASE --DJL USED TO DETERMINE KEY VALUES

SELECT --INFORMATION_SCHEMA.COLUMNS.*

UPPER(col.TABLE_NAME) AS TABLE_NAME,

UPPER(COLUMN_NAME) AS COLUMN_NAME,

FROM INFORMATION_SCHEMA.COLUMNS AS col

join INFORMATION_SCHEMA.TABLES ON col.TABLE_NAME=INFORMATION_SCHEMA.TABLES.TABLE_NAME

WHERE --lower(col.COLUMN_NAME) like '%companyid%' and

INFORMATION_SCHEMA.TABLES.TABLE_NAME not like 'hop_trace1%' and

INFORMATION_SCHEMA.TABLES.TABLE_NAME not like 'MSmerge%' and

INFORMATION_SCHEMA.TABLES.TABLE_NAME not like 'MSys%' and

INFORMATION_SCHEMA.TABLES.TABLE_NAME not like 'sysdiagrams%' and

INFORMATION_SCHEMA.TABLES.TABLE_NAME not like 'view%' and

INFORMATION_SCHEMA.TABLES.TABLE_TYPE='BASE TABLE'

ORDER BY col.TABLE_NAME,

select *

from syscolumns

where [name]= 'UsrID'

select distinct a.name

from sysobjects a

where a.xtype = 'TR'

and a.id not in (

select b.id

from syscomments b

where patindex( '%not for replication%', b.text ) >= 1

group by b.id

)

select * from syscomments where text like '%not for replication%'

--CHECK THE SIZE OF DATABASE TABLES - DOES NOT WORK IN SQL 2005 (only in sql 2000)

-- SELECT CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS 'Table',

-- CAST(rowcnt as varchar(6)) AS '#Rows',

-- reserved * 8 AS 'Disk - Kb',

-- dpages * 8 AS 'Data - Kb',

-- (sum(used) * 8) - (dpages * 8) AS 'Index - Kb'

-- FROM sysindexes

-- WHERE indid IN (0,1)

-- AND OBJECTPROPERTY(id, 'IsUserTable') = 1

-- GROUP BY id, rowcnt, reserved, dpages

-- ORDER BY rowcnt desc;

DBCC CHECKIDENT (COMPANIES)

--select tbl = object_name(parent_obj), fk_name = name

--from sysobjects

--where xtype = 'F' and objectproperty(id, 'CnstIsNotRepl') = 0

--order by tbl, fk_name

--

--

--SELECT sysobjects.name as TableName, syscolumns.name as ColumnName,

--syscolumns.*

--FROM sysobjects INNER JOIN

--syscolumns ON sysobjects.id = syscolumns.id

--WHERE (sysobjects.name like 'ti%')

--and colstat & 0x0001 = 1

--and colstat & 0x0008 = 0

--

--select *, table_name, constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

--where constraint_type = 'foreign key'

--and objectproperty(object_id(constraint_name),'CnstIsNotRepl') = 0

--

--Select o.name from sysobjects o

--where o.type = 'U' And (objectproperty (o.id, 'TableHasIdentity') =1

--Or objectproperty (o.id, 'TableHasTimestamp') =1)

--order by o.name

--

--

--Select o.name, c.name from sysobjects o

--INNER JOIN syscolumns c on o.id =c.id

--Where o.type ='U' and (objectproperty (o.id, 'TableHasIdentity') =1

--and columnproperty (o.id, c.name, 'IsIdentity') =1)

--order by o.name, c.name

-----------------------------------------------------------------------------------------------------------------

--

-- djl 2009-01-27 view TABLE SIZE IN THE DATABASE

--

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

--

-- djl 2009-01-27 view TABLE FIELD DEFINITIONS in all DATABASE TABLES

--

---------------------------------------------------------------------------------------------------------------------------

-- MERGE replication or any type of replication in sql server...changes the modified

-- date of a table in sql. So I tried to determine the modified date of the actual fields

-- in each table. It appears SQL 2005 does not have any of these features. The

-- feature to reveal the date a individual user defined field on a user defined table

-- was modified or created

---------------------------------------------------------------------------------------------------------------------------

-- SEARCH ENGINE CRITERIA

--sql 2005 table column creation date

--sql 2005 table field when created

--sql 2005 table schema field modified date

--sql 2005 sys.objects table field modified date for replicated tables

--sql 2005 sys.objects table field modified date

--how to determine the date modify of sql table column 2005

--sys.columns how to determine the date modify of sql table column 2005

--INFORMATION_SCHEMA date modify of sql column

--INFORMATION_SCHEMA how to determine the date modify of sql table column 2005

-----------------------------------------------------------------------------------------------------------------

DECLARE @TableName varchar(100)

SELECT @TableName = 'registration'

-- This will determine if we're using version 9 (2005) of SQL Server, and execute code accordingly

IF CAST(SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar),1,1) as int) >= 9

BEGIN -- This is a SQL 2005 machine