Querry command questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Querry command questions

Author

Topic

sql2020
Yak Posting Veteran

54 Posts

Posted - 2010-07-16 : 03:06:40

Just wondering if you guys know how to translate this Oracle statement to a SQL statement that works….. thanks ….

I had heard there were issues with the truncate like function and SQL and I wanted to know what you guys knew about it . . . if indeed there is one.

Needs to be run for following Views:

PDL:ESIDmanufacturer (2.1 & 7.5 or later)
PDL:File (2.1 & 7.5 or later)
PDL:ESIDfiles (2.1 & 7.5 or later)
PCT:Product Catalog (2.1 & 7.5 or later)
PCT:Product Alias (2.1 & 7.5 or later)
PCT:ProductCompanyAssociation (2.1 & 7.5 or later)
PCT:Product Model-Version (7.5 or later)
PCT:ModelVersion Patch (2.1 & 7.5 or later)
PDL:ESIDapps (2.1 & 7.5 or later)
PDL:ESIDsuites (2.1 & 7.5 or later)
PDL:Suite_PDAssociation (2.1 & 7.5 or later)
PDL:ESIDsuiteapps (2.1 & 7.5 or later)
PDL:PD_FilesLookup (2.1 & 7.5 or later)
PDL:ESIDappfiles (2.1 & 7.5 or later)
PDL:ESIDversioninfo (2.1 & 7.5 or later)

Note: If you are database proficient this can be achieved by using the truncate table command on data base level to clear all 13 forms (all T and H tables).

For example to get the matching view name of a schema on Oracle db:

select VIEWNAME from arschema where VIEWNAME like 'PDL%';

VIEWNAME
------------------------------------------------------------------------------
PDL_Attachments
PDL_ESIDappfiles
PDL_ESIDapps
PDL_ESIDfiles
PDL_ESIDmanufacturer
PDL_ESIDsuiteapps
PDL_ESIDsuites
PDL_ESIDversioninfo
PDL_File
PDL_MenuItems
PDL_PD_FilesLookup

PDL_SLIInterface_Create
PDL_SoftwareLibraryItem
PDL_Suite_PDAssociation
PDL_VendorVersion
PDL_FileTableLookup
PDL_ProductModelVersion
PDL_SLIInterface
PDL_ProductDictionary
PDL_PDInterface
PDL_ProductDictionaryPatch
PDL_ProductsInSuiteLookup

PDL_SuitesRelatedToLookup
PDL_PDPInterface
PDL_ESIDappfilesCustom
PDL_ESIDappsCustom
PDL_ESIDfilesCustom
PDL_ESIDmanufacturerCustom
PDL_ESIDsuiteappsCustom
PDL_ESIDsuitesCustom
PDL_ESIDversioninfoCustom
PDL_CloneEntry
PDL_ESIDImportStatus

33 rows selected.

select SCHEMAID from arschema where VIEWNAME = 'PDL_ESIDappfiles';

SCHEMAID
----------
445

Get the number of records in that schema:

select count(*) from PDL_ESIDAPPFILES;

COUNT(*)
----------
233784 /* This should return the number of records in schema query.

truncate table B445;
truncate table H445;
truncate table T445;

select count(*) from PDL_ESIDAPPFILES;

COUNT(*)
----------
0 /* This should now return 0 records as well as the schema query.

sql2020

Sachin.Nand

2937 Posts

Posted - 2010-07-16 : 09:01:40

Something like this


select * from INFORMATION_SCHEMA.VIEWS 
where TABLE_NAME='viewname'

SELECT SCHEMA_ID('dbo')
FROM  INFORMATION_SCHEMA.VIEWS
where INFORMATION_SCHEMA.VIEWS.TABLE_NAME  ='viewname'

The count query is same in SQL Server.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources