Christian - October 2010 Entries

Posts: 394
Comments: 124
Trackbacks: 0

October 2010 Entries

SQL-Skript zur Ausgabe vieler Informationen über die Tabellen einer Datenbank

Fürs Archiv hier ein Skript welches viele Informationen über die Tabellen einer Datenbank ausgibt:

SELECT
DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Qualified Name],
CASE WHEN OBJECTPROPERTY(object_id,'TableHasActiveFulltextIndex') = 0
THEN 'no' ELSE 'yes' END AS [FT index],    --Table has an active full-text index.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasCheckCnst') = 0
THEN 'no' ELSE 'yes' END AS [Check Cnt],   --Table has a CHECK constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
THEN 'no' ELSE 'yes' END AS [Clustered ix] ,--Table has a clustered index.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasDefaultCnst') = 0
THEN 'no' ELSE 'yes' END AS [Default Cnt], --Table has a DEFAULT constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasDeleteTrigger') = 0
THEN 'no' ELSE 'yes' END AS [Delete Tgr], --Table has a DELETE trigger.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasForeignKey') = 0
THEN 'no' ELSE 'yes' END AS [FK Cnt],     --Table has a FOREIGN KEY constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasForeignRef') = 0
THEN 'no' ELSE 'yes' END AS [FK Ref],      --referenced by a FOREIGN KEY constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasIdentity') = 0
THEN 'no' ELSE 'yes' END AS [Identity Col], --Table has an identity column.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasIndex') = 0
THEN 'no' ELSE 'yes' END AS [Any index],    --Table has an index of any type.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasInsertTrigger') = 0
THEN 'no' ELSE 'yes' END AS [Insert Tgr],   --Object has an INSERT trigger.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasNonclustIndex') = 0
THEN 'no' ELSE 'yes' END AS [nonCl Index], --Table has a nonclustered index.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
THEN 'no' ELSE 'yes' END AS [Primary Key], --Table has a primary key
CASE WHEN OBJECTPROPERTY(object_id,'TableHasRowGuidCol') = 0
THEN 'no' ELSE 'yes' END AS [ROWGUIDCOL],   --ROWGUIDCOL for uniqueidentifier col
CASE WHEN OBJECTPROPERTY(object_id,'TableHasTextImage') = 0
THEN 'no' ELSE 'yes' END AS [Has Blob],     --Table has text, ntext, or image column
CASE WHEN OBJECTPROPERTY(object_id,'TableHasTimestamp') = 0
THEN 'no' ELSE 'yes' END AS [Timestamp],     --Table has a timestamp column.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 0
THEN 'no' ELSE 'yes' END AS [Unique Cnt],    --Table has a UNIQUE constraint.
CASE WHEN OBJECTPROPERTY(object_id,'TableHasUpdateTrigger') = 0
THEN 'no' ELSE 'yes' END AS [Update Tgr]     --Table has an Update trigger.
FROM sys.tables t
ORDER BY [Qualified Name]

zweite Variante:

SELECT      OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
            T.[name] AS [table_name], AC.[name] AS [column_name],
            TY.[name] AS [data_type],
            AC.[max_length],
            ac.is_nullable as [is nullable],
            ac.is_identity as [is identity]

FROM       sys.[tables] AS T
            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
            INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
            AND AC.[user_type_id] = TY.[user_type_id]

WHERE T.[is_ms_shipped] = 0

ORDER BY T.[name], AC.[column_id]

Quelle: http://www.simple-talk.com/sql/t-sql-programming/exploring-your-database-schema-with-sql/

posted @ Thursday, October 21, 2010 8:49 PM | Feedback (0)

Entwicklerhandbuch für Internet Explorer 9 Beta

Microsoft hat ein Entwicklerhandbuch für Internet Explorer 9 Beta ins Netz gestellt:

"Das Entwicklerhandbuch für Internet Explorer 9 Beta gibt Einblick in die Funktionen und Verbesserungen in Internet Explorer 9 Beta. Mithilfe dieses Handbuchs können Webentwickler und -designer entsprechende Vorbereitungen treffen, damit diese Verbesserungen optimal genutzt werden können. Entwickler können die Plattform zudem in der Internet Explorer-Testversion in Aktion erleben. Websites, auf die im vorliegenden Entwicklerhandbuch verwiesen wird, liegen möglicherweise in englischer Sprache vor."

http://msdn.microsoft.com/de-de/ie/ff468705.aspx?ocid=soc-n-de-ie9--de

posted @ Wednesday, October 20, 2010 6:07 AM | Feedback (0)

Fun mit T-SQL und primary keys:

Hier ein paar Code-Snipptes die man immer mal wieder brauchen kann

Alle primary key einer Datenbank ausgeben:

select t.name, fc.name
from sys.key_constraints as fc
inner join sys.tables as t ON fc.parent_object_id = t.object_id
where fc.type_desc='PRIMARY_KEY_CONSTRAINT'

Ausgabe des primary key der Tabellen einer Datenbank sowie deren primary key Spalte:

SELECT tc.table_name, tc.Constraint_name, ccu.column_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE tc.CONSTRAINT_TYPE = 'Primary Key'

Alle Primary Key und Foreign Key einer Tabelle ausgeben:

SELECT Table_Name as [Table], Column_Name as [Column],
Constraint_Name as [Constraint], Table_Schema as [Schema],*
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE Table_Name = 'TabellenName'
ORDER BY [Table], [Column]