I have added alias datatypes using sp_addtype and would like to view the properties defined in it.
please let me knowhow to view the properties of them and also is there a way to list all alias datatypes existing in the current database along with their properties.
I changed a datatype on a table column. I expected this revision to be reflected in the table view column but the old datatype shows. I'm using SQL Server Express. Is there something I should specify when editing the column so I don't have to drop/add the view? (btw, column has an alias in the view)
View 5 Replies View RelatedHi
How (if i can) can i use a alias in my view
if i have 1 alias total and a alias Moms
can't i then have a nother alias there say total+moms?
Or shall i write all again in another alias
Alvin
Hello Everyone,
I am creating a Metadata management application for a business intelligence platform on SQL Server 2005.
For this purpose, I've set out to consolidate all DB-object metadata (Databases, Tables, Columns, Views) into a single repository that business users can browse through. It has been fairly straightforward so far, but I've hit a wall in the views department.
In fairly simple terms, I want to know which physical columns are selected in a view, with the twist of also knowing the columns' alias. This seems to be impossible as far as I can tell, it is easy enough to get both of these independently, but I can't figure out how to connect an alias to an actual column.
(I prefer to use the metadata catalog views to the INFORMATION_SCHEMA as I need to know the object_id's)
Aliases are easy enough to obtain:
select object_id, column_id, name
from sys.columns
where object_id = object_id('SomeViewsName');
As are the 'physical' columns:
SELECT
t.object_id as TABLE_OID,
c.column_id as COLUMN_OID,
c.name
FROM
sys.views v
JOIN sys.sql_dependencies d
ON d.object_id = v.object_id
JOIN .sys.objects t
ON t.object_id = d.referenced_major_id
JOIN sys.columns c
ON c.object_id = d.referenced_major_id
AND c.column_id = d.referenced_minor_id
WHERE
d.class < 2 AND
v.name = 'SomeViewsName';
As I've said before, the problem is joining these two datasets. One of the problems associated with this is that the latter query also returns columns used in JOIN statements, even if they are not projected in the select part of the view definition.
Ultimately I'd like to get this result:
Alias used in View, physical table's object id, physical column's id
Am I missing something?
Thank you very much in advance for your help.
Hi!
I have created a view and one of the columns in the view has an alias assigned to it.
I'm able to read the metadata from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and also lookup
from which table each column in the view orginated from except for the column that has an alias assigned to it.
Is there any other way to lookup a column that has an alias assigned to it?
Thanks alot!
Adam
Can anyone help me with displaying several table properites at once. I know I can use sp_help 'tablename' to get one at time. What is the best method to get several databases at a time?
View 5 Replies View RelatedHow can we use Update Properties on View? How update properties deals with multiple tables in view.
View 1 Replies View RelatedI am using SqlServer 2005 and I have created small sample table with culumns that have Extended properties.
FirstName have [First Name] extended propery called M_Caption.
Then I created a view from the table to use in an access database.
My problem is, I can't get the table column extended properties to show up in the view column extended properties.
Here is the table
/****** Object: Table [dbo].[Person] Script Date: 04/07/2008 14:42:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[Id] [int] NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'First Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'FirstName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'Last Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'LastName'
I created this view
/****** Object: View [dbo].[View_1] Script Date: 04/07/2008 14:50:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_1]
AS
SELECT FirstName, LastName
FROM dbo.Person
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "Person"
Begin Extent =
Top = 6
Left = 38
Bottom = 99
Right = 189
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
Thanks
Hi!
Sudenly I stoped being able to view sql server object properties in the visual studio properties window . When I selected an sql server object in the server explorer i could see its properties in the prop. window . Now I can't see them anymore, the properties window apears blank.
Can anyone tell what do I need to do to be able to view them again.
Thanks & Best Regards
Pedro Costa
We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server. Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects? Or does Server3 need to have DBInstance1 & DBIstance2? Basically, is the alias just for the database or for the instance too? Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?
View 16 Replies View Related
I'm using ADOX 2.8 for table creation: The following is an example of a column defintion:
If CreateNewTable Then CreateNewTable = a.CreateColumn("ReferenceCount", ADOX.DataTypeEnum.adInteger)
If CreateNewTable Then CreateNewTable = a.CreateColumn("Document", ADOX.DataTypeEnum.adLongVarBinary) 'Oleobject
If CreateNewTable Then CreateNewTable = a.CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18) 'text
Where CreateColumn looks like this:
Public Function CreateColumn(ByVal ColumnName As String, ByVal Datatype As ADOX.DataTypeEnum, Optional ByVal Size As Integer = 0) As Boolean
'ADOX.CreateColumn - Called by Common.CreateNewTable
'CreateColumn creates a column described in the Table object so it assumes it is set.
'One method of setting it is to call Select Table after opening the database
If Not Me.ConnectionIsOpen Then
MsgBox("CreateColumn - Failed to Create Column : " _
& ColumnName, MsgBoxStyle.Exclamation, cNoConn)
Return False
End If
Dim col As New ADOX.Column
col.Name = ColumnName
Try
col.Type = Datatype
Catch e As Exception
MsgBox("CreateColumb - Failed to Create Column : " _
& ColumnName, MsgBoxStyle.Exclamation, e.Message)
col = Nothing
Return False
End Try
If Size <> 0 Then col.DefinedSize = Size
Try
Table.Columns.Append(ColumnName, Datatype)
Catch e As Exception
If Err.Number() <> 0 Then
MsgBox(Err.Source & "-->" & Err.Description, , "Error")
End If
MsgBox("CreateColumb - Failed to Append Column : " _
& ColumnName, MsgBoxStyle.Exclamation, e.Message)
Return False
End Try
col = Nothing
Return True
End Function
in CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18)
the 18 specifies the field width in the database. Yet no matter whether I use adWChar or
adVarWChar, Access always shows the field size to be 255.
Does anyone know why or how to fix that?
Is there any way in SQLServer (2000) to setup an alias to another SQLServer database?
What I want/need to do is this:
.i have a database for each project but many of the tables schemas/triggers/stored procedures/etc. are common so what I want to do is run one set scripts. I want to essentially set up a project structure so that all specific app databases also include some core stuff.
.for example, consider a Users table (simple example). For two applications we have exactly the same users structure, sprocs, triggers, etc. but we have physically different data. I just want to run the same ddl.
.but the users table has a log associated with it in an associated log database. The trigger (and some historical queries) refer to the explicit log database (App1Log.Users, for example).
.what i'm figuring is that if i could set up an alias to the log database and have the sql code refer to that I'd be able to reuse all the code very neatly:
set logDBAlias = 'App1Log'
insert into logDBAlias.Users etc....
.trouble is I cannot figure out how to alias the log database!
Any ideas?
Thank you.
John
How would I create a database alias name for the following databasetestingdatabase.comThis database exisist on a remote server. I have tried using the databasename asserver.[testingdatabase.com].dbo.tablewere server is a linked server using sp_addlinkedserver[testingdatabase.com] is the database namedbo is the instantancetable is the name of the table.using select * from server.[testingdatabase.com].dbo.table producing thefollowing error.unspecified errorI cannot change or rename the database for many reason's (Original programerhardcoded this in a compiled app) don't have access to source code anyway.I have not found any docs on database alias, found docs for server / tablesetc.ThanksConrad
View 4 Replies View RelatedHi,Is there any way to set up an alias for a database within an SQL script?What I'm trying to acheive is... instead of having -<DatabaseName>.dbo.table, <DatabaseName2>.dbo.table, etc. - throughout thescript, I just set up an alias at the top of the script and reference them.TIABen
View 3 Replies View RelatedGood day,
Our SQL Server 2005 installation has an active database that periodically gets rolled over to an empty database with a new name. The software that inserts and updates the database is aware of the name of the active database. Query users use a shell database with a static name, and it has views into the active database. All this works fine.
Our question is regarding backup. Is there any way to establish a databse name "alias", so that backup routines can remain unchanged but point to the active database?
Thanks in advance...
can any body help me on creating alias of database using script.
thanks in advance
Regards
Ganesh Gorla
What are the differences between the database permissions, which can be granted in the database properties permissions tab (create table, create procedure etc.) and the predefined role db_ddladmin? It seems that the database properties permissions tab includes more permissions than the predefined role db_ddladmin.
Does anybody know the difference in terms of permission?
I have an ODBC connection string that is working fine with the following properties:
Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.
I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.
Under Registered server name I've tried:
YYYYYYXXXXX
When I browse the server for the database instance list, I receive "network path was not found".
I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.
What am I doing wrong ?
is there anyway to retrieve the database properties and display in an asp.net web form as a quick over view rather than logging onto the server to check, items such as size, space available, last backup data etc? Cheers
View 2 Replies View RelatedWhen I create a new database it asks for information regarding the Data files.
What is it for? Will this file get creted to the location that I write?
How often will it get copied there?
Hi.
I am trying to look for ways on how to get all the databse properties under each instance.
say I want to view the size, recovery model, physical location, etc.
The sys.database_files is only applicable per database. Is there a query, or a table in master database to get these or store these values?
thanks...
I am trying to find information on Database properties - more specifically, what the 'Space Available' means, because right now when I look at the properties of a database I am setting up, which will grow substantially I am sure, it says the database size is 154 MB and the Space Available is 42 MB. What is the Space Available? What does this refer to and is it going to be restricting the growth of the database? Thanks.
View 7 Replies View RelatedHi,I'm creating a User Interface to display Sql database Properties, but I cannot find the right query to retrieved the info on database properties. The status that I get is "ONLINE", but it should be "NORMAL". Cannot find the right query to get the date of last database backup, last transaction log backup, and maintenance plan, etc.. Please help me [:'(]
View 2 Replies View RelatedHello, Everyone:
There are some databases in a SQL Server. How to check,
1. User database numbers
2. Backup plan
3. Configuration
4. User database size
5. Nightly process
Thanks a lot
ZYT
I've been asked to provide some information about databases on an sql server.
I can use the following query to provide the names of all the databases:
select * from master.dbo.sysdatabases
What I'm also looking to provide is the current "size" of the database. I'm trying to find a table that I can link to that would provide the database size. I can manually find this number by right clicking on the database from enterprise manager, selecting "properties" and picking it up from the general tab.
This is sql server 2K...
Thanks.
database role properties/ permissions
when i click permissions it takes forever to pop up the window.
Anyone know why and how do i make it faster.
http://www.sqlserverstudy.com
Hello there,I have about 30 sql servers runnign multiple databases amd i need toaudit them so what i want to do is to log onto each server and thenmaybe export the database properties into an excel sheet or text file.Anyone know how to do that i am quite new to sql any help will beawesome.Regards and thanx in advance
View 2 Replies View RelatedHowdy folks!
If I set the following three properties when creating a database, do I need to set the same properties when I open the same database later?
sscedbprop[0].dwPropertyID = DBPROP_SSCE_AUTO_SHRINK_THRESHOLD;
sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[0].vValue.vt = VT_I4;
sscedbprop[0].vValue.intVal = 100;
sscedbprop[1].dwPropertyID = DBPROP_SSCE_MAX_DATABASE_SIZE;
sscedbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[1].vValue.vt = VT_I4;
sscedbprop[1].vValue.intVal = 4091;
sscedbprop[2].dwPropertyID = DBPROP_SSCE_ENCRYPTDATABASE;
sscedbprop[2].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[2].vValue.vt = VT_BOOL;
sscedbprop[2].vValue.boolVal = (a_bEncrypted==TRUE)?VARIANT_TRUE:VARIANT_FALSE;
Thanks!
BTW, why is the max size not 4096?
Due to a network time skew some time ago, all of the databases on an SQL system now have the following information stamped on them..
Within Enterprise Manager (SQL 2000), The "Last Database Backup" timestamp for each database is showing the year 2025.
Our backup system runs incrementally daily and a weekly full. Unfortunately, the timestamp does not seem to be updating as the year is as I guess in the future.
Can anyone advise of a procedure so as I can wipe this time skew date from each of the databases?
Many thanks folks!
I am trying to generate a report from the database. I am using Visio 2010. I would like to include the table names, column names , column types and the comments that I entered in the NOTES field associated with the tables and columns. Basically, I would like to generate a data dictionary report.
View 2 Replies View Related
Can anyone help?
When I select the database and right click. I receive the following error:
Cannot show requested dialog
Additional Information:
Cannot show requested dialog (sqlMgmt)
Property Owner is not available for Database '[DB_name]'. This property may not exist for this object,
or may not be retrievabledue to insufficient access rights. (Microsoft.SqlServer.Smo)
I am using SQL server 2005.
I'm wondering if there is some sql I can run to check properties on a table. This would be used to verify things like data types, allow nulls and default values have been set to avoid mistakes. This could be done manually one table and one column at a time, but it would be a lot easier to look at it in the results window.
View 5 Replies View RelatedI have just upgraded to SQL Server 2005 from SQL Server 2000.In Microsoft SQL Server Management Studio, when I click on databaseproperties, I receive the following error:-Cannot resolve the collation conflict between"Latin1_General_CI_AS"and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.(Microsoft SQL Server, Error: 468)Some reference suggest that I can change the database collation byclicking database properties!What can I do?
View 21 Replies View Related