I had trouble getting the OLEDB Provider from IBM Client Access to work. It was pulling data, but due to CCSID issues, many columns didn’t translate properly. To resolve this, I had to use a T-SQL query so I could set Force Translate=0, when adding the LinkedServer. Continue reading “Adding LinkedServer to AS400/DB2”
Clearing All Rows From All Tables
This couple of SQL statements will delete all the rows from all tables.
- It disables referential integrity
- DELETES or TRUNCATES each table
- Enables referential integrity
- Reseeds rows with identity
— disable referential integrity
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
GO
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
‘
GO
— enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO
— This will reseed each table [don’t run this exec if you don’t want all your seeds to be reset]
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED, 0)
‘
GO
Thanks to Mauro Cardarelli’s post
Find Which Table(s) Contain a Column and the INFORMATION_SCHEMA Namespace.
To find which table(s) contain the PersonID column try this:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘%PersonID%’
In the AdventureWorks DB this is what you see:
So of course you can search for any column, not just PersonID.
Another interesting tidbit is that any ANSI compliant DBMS provides the INFORMATION_SCHEMA namespaces. From this namespace you have access to the metadata on any DB object. You can look up information on Stored Procedures and Functions using INFORMATION_SCHEMA.ROUTINES or use any of the many different objects within this namespace: COLUMNS, ROUTINES, CHECK_CONSTRAINTS, PARAMETERS, TABLES, etc.
Enjoy