How To Get The Metadata Of A Table
May 11, 2007As title, such as the PK, data type of each column, etc.
Thanks,
Ricky.
ADVERTISEMENT
Transpose Source Data From A System Via Metadata Lookup Table Into Destination Table
Apr 1, 2014I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.
Source Data Table:
Table Name: Source
SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013
Metadata Table:
Table Name:Metadata
MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date
Destination table:
The source data to be loaded into the destination table(as shown below):
Table Name: Destination
SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013
Update Target Table Using Metadata And Source Table
Oct 4, 2013I'd like to figure out how to use the @FieldDescription table below as an intermediate table between the @SourceData and @Stops data.
declare @Stop table (StopId int, UserField varchar(20))
declare @FieldDescription table (Label varchar(10), ColumnName varchar(10))
declare @UpdateSource table (HasPathway varchar(10))
insert into @Stop (StopId, UserField)
values (1, 'Yes')
[code]...
I want to update @Stop.UserField with thevalue from @UpdateSource where @UpdateSource.HasPathway=@Stop.UserField...but I need to use the @FieldDescription table to determine how to map the columns.
SQL Table And Column Metadata
Jun 21, 2006SQL table and column metadata
Is there a way to add or update the column or table (using the extended properties) description metadata via T-SQL (from within a stored procedure) or via a program (such as VB.NET using ADO)?
These metadata properties are available via the SSMS interface:
Columns via the Column Properties/Table Designer/Description
Tables via the Table Properties/Extended Properties/[Extended Property Name]
Thanks in advance,
Mark
Integration Services :: Column Metadata In Table
Apr 10, 2015I am having one store procedure which use to load data from flat file to staging table dynamically.everything is working fine. staging_temp table have single column.all the data stored in that single column below is the sample row.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯Â
after the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.if you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.output should be like below.
AB¯ALBERTA ¯93
AI¯ALBERTA INDIRECT ¯94
AL¯ALABAMA ¯30
Loop Through A Table Which Has Table Metadata
Feb 18, 2015I am trying to loop through a table which has table metadata and create a:
- 'STORED PROCEDURE'
- This will SELECT all the data in the table and add a hashed column at the end
- Each table has a unique ID
-
Instead of a cursor would there be a set-based approach to achieving this?
METADATA TABLE :
IDTableNameColumnNameHashByteCalculation
111dbo.TableAColA CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColBCAST(ISNULL(LEFT(CONVERT(VARCHAR,ColB, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColCISNULL(ColC,'NA') + '|' +
111dbo.TableAColDISNULL(ColD,'NA') + '|' +
222dbo.TableBColAACAST(ISNULL(LEFT(CONVERT(VARCHAR,ColAA, 120), 10),'NA') AS varchar) + '|' +
222dbo.TableBColBBISNULL(ColBB,'NA') + '|' +
222dbo.TableBColCCISNULL(ColCC,'NA') + '|' +
From the above data I want to generate:
SELECT
ColA
,ColB
,ColC
,ColD
, (CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' +
[Code] ....
Can I Query Metadata To Find Table The Owns Field?
Mar 9, 2008I have to write some reports for a database I am not familiar with. Is there a query I can use to find a table name if I know the field name?
example: Select table_name from database where field_name = 'my_field'
Mike
Metadata Driven SSIS Package--table Variable
Aug 16, 2005Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!
View 12 Replies View RelatedExtracting Table Metadata Like Schema / Catalog Name From Memory / Files?
Jun 29, 2012I wanted to know that when I create a table using SQL Server, does the table metadata like schemaname and catalogname is stored in memory or in hard-disc. I am trying to extract schemaname and catalogname when the database is down or correctly when I have no connection string. I know about sysindexes, systables etc, but i am not able to understand whether it stores the data which is useful to me like catalogname.
sql query to extract the column names from sysindexes?
Store Procedure To Load Data From Flat File To Staging Table Dynamically - Column Metadata
Apr 9, 2015I am having one store procedure which use to load data from flat file to staging table dynamically.
Everything is working fine.staging_temp table have single column. All the data stored in that single column. below is the sample row.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
After the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.
If you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.
Output should be like below.
AB¯ALBERTA ¯93
AI¯ALBERTA INDIRECT ¯94
AL¯ALABAMA ¯30
How to achieve above scenario...
MetaData
Jul 20, 2005What is "MetaData" in "Data Transormation service" and what is"MetaData services" ?and in which field I can use them?Thanks
View 1 Replies View RelatedInsconsistant Metadata
Aug 22, 2001Hi All!
I recently added a column to an existing table with a getdate default. When doing a query from that server everything works fine. When a query is ran from a remote server I get an SQLOLEDB error message saying 'inconsistant metadata'. I've tried dropping the remote server and reconnecting but that didn't seem to resolve the problem. Can anyone tell me how to resolve this error. I believe the error number is 7353.
Any suggestions appreciated!
Thanks Jeff!
Retrieving Metadata
Jul 29, 2005Hi,Is it possible to get metadata (i.e. descriptions of tables etc.) insql-server? In Oracle you can retrieve this information with tables likeall_objects, user_tables, user_views etc. For example, this query selectsthe owner of the table 'ret_ods_test' (in Oracle!):select ownerfrom all_objectswhere object_name = 'ret_ods_test'What's the equivalent in sql server?Thanks a lot.
View 2 Replies View RelatedSSIS Metadata...
Mar 18, 2007hi,
Is there a way to find out which user defined procs/child packages etc are been called in SSIS packages using some metadata. The idea is to have a document which lists the number of packages called, whats sprocs and child packages are executed by those pkgs..
I have checked the SSIS metadata whitepaper but that is too generic.
Metadata Refresh?
May 15, 2006What do you do to address this:
[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.
A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?
SSIS Metadata
Feb 26, 2008I'm working on capturing metadata from my SSIS packages. I have found multiple postings and a white paper that
reference a download toolkit for just this purpose. The link is dead and a search renders no results - can anyone help?
SQL Server 2005 Business Intelligence Metadata Whitepaper
Metadata Refresh
Feb 26, 2007-We are using SSIS packages for various kind of data load from excel source.
-If there are any change in the data type or format of excel, the package cries for the Metadata mismatch.
-During design time if you accept the metadata changes, all things work fine.
But in our case we have deployed the packages on Production Server, now the excel file format/data has changed. The packages are expecting a different metadata so they are not working at all.
Do you have any suggestions for the above problem?
Thanks, Vijay.
Metadata Question
Apr 4, 2008i am using icolumnsrowset interface to get some metadata about the columns in a rowset, but i never got unique and primary key columns in the columnsrowset, they always return null, when using sqlcedataadapter i simply add addwithkey option to the adapter to determine it, but i dont know how to do it by using ole db interfaces, i have tried to set DBCOLUMN_KEYCOLUMN flag to true on ccommand<cynamicaccessor, crowset> but it seems it rejects it, generating an unknown error, error object says almost nothing except that 'errors occured[,,,,,]' text
can someone tell me, how can i retrieve columnsrowset with that unique and primary key sections filled?
Metadata Definition ?
Apr 5, 2006Hello, i would like to know if it's possible to generate automatically a word document or an excel document that will contain all the metadata definition, for example containing the source columns names, their datatype, and the destination with their datatypes, so that it would easy to create a data dictionnary .
Thank you in advance.
METADATA DEPENDENCY
Nov 9, 2007Hello everybody,
It's now quite some time that one particular behaviour of SSIS is really frustrating me and I would like to know if I'm the only one experiencing this problem or if other people have the same problem.
The issue I'm talking about is SSIS 'dependency on what is written in the XML files describing the flows.
Particularly with the Data Types of columns.
I'm explaining myself: Imagine your are developping a flow containing several numeric(18,0) columns...
During the flow you have to perform a lookup on an Integer Field.... Of course this operation is not allowed as a numeric is not mappable with an Integer... (This is, in my opinion, a nonsense as an implicit conversion has to be possible).
as a result of this behaviour, I decide to change the datatype (numeric) from my source query to an integer and use it in the Lookup which of course succeeds but now I have a second problem: each lookup in my flow has an error handling branch which I'm joining back using a Union transform. and there we have the second irritation: the Union transform doesn't replicate the Data Type changes that occured upwards in the flow... worse: it even has no interface to let you modify the data types like the advanced editor of some transforms or data sources. (I've just lost a complete dataflow while trying to modify it manually in the xml file directly :-( for those who are considering modifying directly the XML, don't!! You are asking for trouble and a lot of frustration when you'll switch back to the designer to see the effects )
My question is now: Am I misusing SSIS?? Is there somewhere an option to activate in order to get this behaviour fixed??
Has anyone else experienced this problem?? How are you solving this??
Are there any plans in the future to loose this dependency on the datatypes or at least add some implicit conversions??
Thanks in advance for your replies, suggestions,questions and other thaughts about this subject :-)
Alain
MetaData Backup
Feb 12, 2008
Hello-
I have a question regarding my metadata information. I finally setup my fixed width file which took some time. Is there a way that I can backup my metadata so I wont have to recreate these setting again. I'm thinking the format of the file is stored in the metadata so if I have a user running the SSIS package from the Business Intell Studio they wont reset all of my columns. Is there a file I can restore or backup if this should happen
Shanon
Metadata Application
Sep 26, 2007Hi all of you,
I'm focused on writing a SSIS metadata application. I'm trying to get all the metadata for all the DTSX packages from a concrete server.
I'm loading successfully tasks as Execute Sql Task, ForEach task and so on but how could I do the same for
Data Flow Task and its components. ?¿¿
For a Data Flow I don't know how to begin...
If TypeOf tmpTaskHost.InnerObject Is ????????????????? Then
My central code is the following:
sServer = "TEST1"
pkgIn = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)
'''''''''''''''Para cada carpeta creada a partir de MSDB
For Each pkgCarpeta In pkgIn
If pkgIn.Item(y).PackageDataSize <= 0 Then
pkgCarpetaSSIS = app.GetPackageInfos(pkgCarpeta.Name, sServer, Nothing, Nothing)
Else
pkgCarpetaSSIS = app.GetPackageInfos(carpetaraiz, sServer, Nothing, Nothing)
End If
If pkgCarpetaSSIS.Count >= 1 Then
While i < pkgCarpetaSSIS.Count
'cargar en memoria el SSIS en el objeto PAQUETE
pkg = app.LoadFromSqlServer(pkgCarpetaSSIS.Item(i).Folder & "" & pkgCarpetaSSIS.Item(i).Name, _
sServer, Nothing, Nothing, Nothing)
Dim task As Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask
Dim task2 As Microsoft.SqlServer.Dts.Runtime.ForEachLoop
Dim exe As Executable
Dim tmpTaskLoop As ForEachLoop = CType(exe, ForEachLoop)
Dim tmpTaskHost As TaskHost = CType(exe, TaskHost)
For Each exe In pkg.Executables
Select Case exe.ToString
Case "Microsoft.SqlServer.Dts.Runtime.ForEachLoop"
tmpTaskLoop = CType(exe, ForEachLoop)
Case "Microsoft.SqlServer.Dts.Runtime.TaskHost"
tmpTaskHost = CType(exe, TaskHost)
End Select
issue raises here.
If TypeOf tmpTaskHost.InnerObject Is ExecuteSQLTask.ExecuteSQLTask Then
n1 = tmpTaskHost.Properties.Item(33).GetValue(tmpTaskHost)
For y = 0 To 42
Console.WriteLine(tmpTaskHost.Properties.Item(y).Name)
Next
End If
Next
'contador de paquetes
i += 1
End While
y += 1
End If
Next
..
..
..
..
..
Thanks a lot for ideas and thoughts!!!!
Metadata Visibility
Feb 25, 2008Hi,
I'm running the following query in SQL Server 2005:
select name from master..syslogins;
It is being executed from within a stored procedure.
For user 'sa' - I get the complete list of users.
For a user (say 'user1') with NO sysadmin privilege - I get only two names: 'sa' and 'user1'
Is there a way for me to retrieve the complete list of users even for 'user1' without making any changes to his profile (or making very MINIMAL changes to profile)?
I don't want to give sysadmin profile to this user.
I know 'GRANT VIEW ANY DEFINITION TO public' works, but don't want to do that either.
-Anshul
Metadata/Report KB
Jun 26, 2007I wanted to see if anyone has explored posting a Report KB based on metadata in the RS portal? If so, have you found a way to post the information on the RS portal?
I really am looking to add additional properties to a report. Other than author and description. I would like to add 2 to 3 more fields that would feed over to the catelog table on the report server. Then write a few reports that will allow for definitions, metadata, and links to the reports.
Does anyone have any ideas for something like this?
Metadata Update
Dec 28, 2006hi, I try to post a new question about metadata refresh...even if i see other thread that work on a similar problem.
I have a ssis package that import an xml huge file (500 mb); These are the main step:
1) generate a XSD file against xml using xsd.exe utility
2) using xml task, make a diff between the old xsd and the new
3) if there are no difference, I start the data flow task that import xml in sql server; otherwise I stop all the task, edit the data flow task, change the xsd reference in advanced editor and then make many "double clik / OK" on every single flow....
The underling idea is that xml file change because some columns are added but these columns are not interesting for my elaboration, so i can ignore this new column and work without mapping it.
What I'm looking for is a way for make, via SSIS, the "double clik / OK" steps....in other words, to update the metadata.
Could anybody suggest me a way? it's a sort of macro, or keyboard recorder...I'm trying to study xml package configuration; is this a good way ?
another way is to give to the end user the task to update metadata; for making this I need to open the package editor (visual studio..:!) in a more confortable environment....For example, is possible to edit the ssis package in ms access? probably i know the answer...
please, help me!
thank tou in advance
alessandro
Where Is "description" Col In Repository Metadata
Apr 20, 2000I'm trying to create a Data Dictionary view from system table info like tablename, fieldname, datatype etc. I can find all that I need except for the "description" field which is displayed in Enterprise Manager/ Repository metadata pane.
How can I locate this field so I can reference it in a view? Isn't it stored in a system table? Master, model? Where is it?
Thank in advance,
Phil
Metadata - Order By Information
Jun 14, 2006Hey there,Im wondering if there is a way to determine which views in my database use the "order by" statement. The reason I need this is because we need to migrate over to MS SQL 2005 where the order by statements are ignored within the views themselves. Now(in mssql 2005) you need to explicityly state the order by now when calling a view ie. select * from [viewname] order by column x, y desc, z instead of ie. select * from [viewname] where the view already had the applicable sorting done within the view.If those order by statements are ignored, some production software which rely on the ordered data will corrupt.Please let me know if there's a way to query the actual database and determine which views have 'order by' statements in them.thx
View 7 Replies View RelatedAdding Comments To Metadata
Mar 15, 2008Did some searching and didn't seem to find what I'm looking for. I'm pretty new to SQL Server (most of my experience is on DB2 for z/OS).I'm building some new tables, and want to find a way to add comments to the metadata for the column. In DB2 the syntax is:COMMENT ON COLUMN TB_CREATOR.TB_NAME.COLUMN_NAME IS 'comments here';ORCOMMENT ON TB_CREATOR.TB_NAME (COLUMN1 IS ' comment here',COLUMN2 IS ' comment here', );Is there anything like this in SQL Server?Thanks!
View 9 Replies View RelatedMetadata Wont Go Away(sql Server2005)
Dec 12, 2006I'm new to dba'ing so go easy on me. I'm looking after a brand new
installation of sql server 2005. I've created a simple db with 1 table but when I go to MSAccess ( as a newly created user) and create a linked table to sql database all the metadata is showing and I can't stop it.
I've tried going through securables but its still visible no matter how much I try to deny. From what I've read this shouldn't be possible as making metadata invisible should be standard.Can anyone throw any light on this?
SQL Server Views' Metadata
Jul 20, 2005Be careful when implementing views (from SQL Server 97/2K). SQL Serverstores the metadata on the view at creation (or the last time it wassaved). This means if you have:SELECT * FROM table1it will put all the fields of table1 in the view's metadata. If youthen change table1 and add (for example) another field, this fieldwill not be visible in the view until you open it in design view andclick save (to update it).
View 1 Replies View RelatedHelp Files / Metadata On Reports
Aug 29, 2007I had a pervious thread on a metadata KB but didn't get a response. So I was wondering if anyone has added on a web based help file app to SSRS? Something that you could assign additional metadata to each report. Something that would be searchable.
At this point SSRS doesn't provide enough fields for metadata. I am looking to assign some thing like:
1. Owner/Requestor of report
2. Fields in report
3. Description (other than the one provided)
4. etc..
Does anyone have any ideas of a add on, utility, or software?
Thanks
fsugeiger
How To Get Column Description From MetaData?
Mar 25, 2008
I've tried looking in sys.syscolums and sys.syscomments, but I can't seem to find where the Description information is retain for a Field in the system tables -- any hints?
Thanks, Rob.
Incorrect UnPivot Metadata
Jul 27, 2006Hi
When using unpivot transformation, what exactly this error denote
"Incorrect UnPivot metadata. In an UnPivot transform, all input columns with a PivotKeyValue that is set, and are pointing to the same DestinationColumn, must have metadata that exactly matches "
data on which i was trying unpivoting is -
Name
Pd1
Pd2
Pd3
Pd4
Utsav
111
211
311
411
Verma
122
222
322
422
Nucleus
133
233
333
433
Noida
144
244
344
444