SQL Server 2012 :: Removal Of Records Dynamically Using Information Schema
Aug 14, 2015
I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.
I've done already the schemas for all the tables a you suggested.But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.I'd like this dbo not to automatically appear whenever i created a new table — as it requires me to alter whenever I created a new table.Is this something that is required to determine the creator of the table?Otherwise, I'd like that to remove.
Connect as X002548 since logins are on both serves
*/
-- Tables in 14 not in 45
SELECT S14.TABLE_NAME FROM INFORMATION_SCHEMA.Tables S14 LEFT JOIN INFORMATION_SCHEMA.PAERSCBVD0045.dbo.MEP.Tables S45 ON S14.TABLE_SCHEMA = S45.TABLE_SCHEMA AND S14.TABLE_NAME = S45.TABLE_NAME WHERE S45.TABLE_SCHEMA = IS NULL AND S45.TABLE_NAME = IS NULL
Information Schema is the part of the SQL-92 standard which exposes the metadata of the database. I have written a small article that exposes the same. Let me know your comments on the same. http://aspalliance.com/1380_Information_Schema_and_SQL_Server_2005
Uday Denduluri Software Engineer Refer my articles at http://aspalliance.com/author.aspx?uId=62740
I currently have a lot of XML blobs being stored in several of the production databases that I look after. They're there as a application "logging" feature, so that the developers can investigate issues if they ever occur.
I want to get these out of my OLTP databases asap and am currently looking at options. I know Brent Ozar recently posted an article about Content Addressable Storage systems [URL] ....
How do you handle point in time recovery? Any other ways of storing this data?
hi all, I have a duplicate stored procedure one has proper entries in the information_schema and the other with the same name has null entries in information schema meaning , the specific_schema and routine_schema entries are null and now it doesn't allow me to open the stored proc, neither it allows me to delete the proc. while deleting it says" procedure_Name not found in the (NULL) collection. any help will be highly appreciated.
Hello All,Could someone help provide a query that I can run todetermine the primary key and unique columns for anygiven table ?Thanks in Advance,Akintoye
We are designing a server where functions and procedures are to be kept in a single database. Our other databases will call these functions. The functions will need to reference tables in the schema being called from. Is it possible to pass a schema name to a function? If so, then how?
I tried passing something like:
CREATE FUNCTION [dbo].[getPaymentReceived2](@inSchema CHAR(30), @inAraccountid CHAR(15), @inInvoicestart SMALLDATETIME)
RETURNS MONEY
AS
BEGIN
DECLARE @outAmt money
DECLARE @arinvoiceid char(15)
SET @arinvoiceid = (SELECT ai.arinvoiceid
FROM @inSchema.dbo.arinvoice ai
WHERE ai.araccountid = @inAraccountid
AND ai.invoicestart = DATEADD(month, - 1, @inInvoicestart))
I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).
The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.
The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.
The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.
I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT ES.login_name AS LoginName, L.request_session_id AS BlockedBy_SPID, DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec, DB_NAME(L.resource_database_id) AS DatabaseName,
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _ System.Data.OleDb.OleDbSchemaGuid.Columns, _ New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
I am having 2 tables one is staging temp and another is main import table.
In my staging table there are 3 column Col001,Id,Loaddate
in Col001 column data are present with '¯' delemeter.
I am having function which is used to load data from staging to import table using one function.
this function create a insert statement.
My Existing function
-- Description: To Split a Delimited field by a Delimiter ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter] ( @fieldname varchar(max) ,@delimiter varchar(max) ,@delimiter_count int
[Code] ....
I am unable to get correct statement with above function.
I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive, DB_NAME(mf.database_id) AS DatabaseName, db.create_Date, DateDiff(day, db.create_date, getDate()) Age, sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
missing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event.
I am tasked with truncating and reloading tables from one server to another. Company policy prevents cross-server queries, but allows SSIS packages with cross-server connections. I am doing this for about 25 tables. I have the table names in a single table & I have created an FEL to execute tasks against each table one-by-one. It works fine to truncate all the tables. I run into issues, though, with the DataFlowTask. I'm able to tell it which server & table to dynamically connect from and to, but it doesn't know how to map the metadata. They're the exact same columns and field names in both source & destination.
After performing a join operation on two tables i get the below resultset
pid, fname, typename, pname, pcost
1, cad, bars, product-1, 100
2, har, witte, product-2, 120
3, nes, bars, product-3, 119
Now i need to create files with the obtained resultset like
Column 'fname' is the folder name and 'typename' should be the file in the particular folder.
For example the first record should be inserted into file name 'bars.txt' in the folder 'cad' and third record should be created in file name 'bars.txt' in the folder 'nes'.
I am having 100 of flat files need to load in respective staging table.I want to create table on run time as per filename input.suppose if input filename is ABC then table name should be Staging_ABC if file name is XYZ then it should be Staging_XYZ.Table structure is below need to create at run time
CREATE TABLE Staging_'Filename'( [COL001] [varchar](4000) NULL, [Id] [int] IDENTITY(1,1) NOT NULL, [LoadDate] [datetime] NOT NULL default getdate() )
I am getting error when I am trying to create table on runtime
Declare @FileName varchar(100) Declare @File varchar(100) set @FileName='brkrte_121227102828' SET @File = SUBSTRING(@FileName,1,CHARINDEX('_',@FileName)-1) --=select @File
[Code] ....
Error massage:- Msg 203, Level 16, State 2, Line 16
The name 'CREATE TABLE DataStaging.dbo.Staging_brkrte ( [COL001] VARCHAR (4000) NOT NULL, [Id] Int Identity(1,1), [LoadDate] datetime default getdate() )' is not a valid identifier.
I need to copy all objects (views, procedures, tables, functions, etc -- no data) that are under a specific Schema in a database, but the Generate Script option in SSMS doesn't give me the option to do this. How I can generate a script that's Schema specific?
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , i.name AS [Index Name] , i.index_id , user_updates AS [Total Writes] , user_seeks + user_scans + user_lookups AS [Total Reads] , user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
I have a SQL database (10000 records / 50 users) that stores information on faxes that we receive. This will sound odd but the last few days some weird things have happened. Chunks of data change arbitrarily - no script that I have can make these changes. Couple examples:
1) the number of pages will change from 1 to 3 - not every entry just a group and usuall ones that have a particular status 2) the status will change from CTW to PFR - not every CTW will change just a chunk
There appears to be no pattern to these changes.
Again, no script that I have can make these changes. This happened 6 months ago and I reinstalled service packs (NT and SQL), reinstalled data components and defragged the hard drive. The problem stopped either because of one of these actions or either on it's own. I have done all of these things this time but nothing has worked. Anyone have any ideas?? Any help is appreciated.
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. The big issue here is that I have to base my concatenation on 3 columns. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, Bryan Ax
