Comment puis-je retourner les types de données SQL de ma requête?

J'ai une requête SQL qui interroge un énorme (comme dans des centaines de points de vue et les tables sont difficiles à lire des noms comme CMM-RPC-FAP-AJOUTER) de la base de données que je n'ai pas besoin ni envie de comprendre. Le résultat de cette requête doit être stocké dans une table intermédiaire pour nourrir un rapport.

J'ai besoin de créer la mise en scène de la table, mais avec des centaines de points de vue/tables de creuser à travers pour trouver les types de données qui sont représentés ici, je me demande si il ya une meilleure façon de construire cette table.

Quelqu'un peut conseiller comment je pourrais utiliser les outils SQL Server 2008 divine à la source des types de données dans ma base de données SQL 2000?

Exemple général, je veux savoir à partir d'une requête comme:

SELECT Auth_First_Name, Auth_Last_Name, Auth_Favorite_Number 
FROM Authors

Au lieu de les résultats réels, je veux savoir que:

Auth_First_Name is char(25)
Auth_Last_Name is char(50)
Auth_Favorite_Number is int

Je ne suis pas intéressé dans les contraintes, vraiment, je veux juste savoir les types de données.

InformationsquelleAutor JMP | 2009-10-21

54
```
select * from information_schema.columns
```
pourrait vous aider à démarrer.
- Nom d'objet non valide 'information_schema.les colonnes'
- Avez-vous une casse de la base de données? Si oui, vous devez utiliser SELECT * FROM INFORMATION_SCHEMA.Columns
- Oui, il est sensible à la casse. J'ai essayé SELECT * from INFORMATION_SCHEMA.Les colonnes et obtenir le même objet non valide erreur. C'est SQL Server 2000 (8.0.2055), ne INFORMATION_SCHEMA existe pas dans cette version?
- ok nvm, j'ai essayé SELECT * from [nombase].INFORMATION_SCHEMA.COLUNMS et je suis de quelque part. Merci d'être patient avec moi sur ce point!
InformationsquelleAutor erikkallen
24

Vous pouvez également insérer les résultats (ou le top 10 des résultats de recherche) dans une table temporaire et obtenir les colonnes de la table temporaire (aussi longtemps que les noms de colonne sont tous différents).
```
SELECT TOP 10 *
INTO #TempTable
FROM <DataSource>
```
Alors utiliser:
```
EXEC tempdb.dbo.sp_help N'#TempTable';
```
ou
```
SELECT * 
FROM tempdb.sys.columns 
WHERE [object_id] = OBJECT_ID(N'tempdb..#TempTable');
```
Extrapolée à partir de la Aaron réponse ici.

InformationsquelleAutor Trisped
21

Vous pouvez également utiliser...
```
SQL_VARIANT_PROPERTY()
```
...dans le cas où vous n'avez pas un accès direct aux métadonnées (par exemple, une requête de serveur lié peut-être?).

http://msdn.microsoft.com/en-us/library/ms178550.aspx

Dans SQL Server 2005 et au-delà, vous êtes mieux utiliser les affichages catalogue (sys.les colonnes), par opposition à INFORMATION_SCHEMA. À moins que la portabilité vers d'autres plates-formes est important. Il suffit de garder à l'esprit que le INFORMATION_SCHEMA points de vue ne changera pas et donc ils seront progressivement manque d'informations sur les nouvelles fonctions, etc. dans les versions successives de SQL Server.
- Cela ne fonctionnera pas pour les champs de texte. Va jeter cette exception: "Opérande type de choc: le texte est incompatible avec sql_variant"
- Merci, rappelez-vous ceci est un cas exceptionnel (quand vous n'avez pas accès aux métadonnées) et, même en 2009, lorsque cette question, vous devriez avoir abandonné text, ntext, et image. Si les gens sont toujours à l'aide de ceux d'aujourd'hui, ce n'est qu'une petite partie de leurs problèmes...
InformationsquelleAutor Aaron Bertrand

Il DOIT être en moyen plus facile de faire cela... Faible et voici, il y a...!

"sp_describe_first_result_set" est votre ami!

Maintenant, je ne réalise la question a été posée spécifiquement pour SQL Server 2000, mais je cherchais une solution similaire pour les versions ultérieures et la découverte de l'un support natif de SQL pour atteindre cet objectif.

Dans SQL Server 2012, cf. "sp_describe_first_result_set" - Lien de BOL

J'avais déjà mis en œuvre une solution en utilisant une technique similaire à @Trisped de l' ci-dessus et le déchira pour mettre en œuvre le natif SQL Server mise en œuvre.

Dans le cas où vous n'êtes pas sur SQL Server 2012 ou SQL Azure Base de données, voici la procédure stockée que j'ai créé pour le pré-era 2012 bases de données:

CREATE PROCEDURE [fn].[GetQueryResultMetadata] 
    @queryText VARCHAR(MAX)
AS
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --SET NOCOUNT ON;

    PRINT @queryText;

    DECLARE
                @sqlToExec NVARCHAR(MAX) = 
                    'SELECT TOP 1 * INTO #QueryMetadata FROM ('
                    +
                    @queryText
                    +
                    ') T;'
                    + '
                        SELECT
                                    C.Name                          [ColumnName],
                                    TP.Name                         [ColumnType],
                                    C.max_length                    [MaxLength],
                                    C.[precision]                   [Precision],
                                    C.[scale]                       [Scale],
                                    C.[is_nullable]                 IsNullable
                        FROM
                                    tempdb.sys.columns              C
                                        INNER JOIN
                                    tempdb.sys.types                TP
                                                                                ON
                                                                                        TP.system_type_id = C.system_type_id
                                                                                            AND
                                                                                        -- exclude custom types
                                                                                        TP.system_type_id = TP.user_type_id
                        WHERE
                                    [object_id] = OBJECT_ID(N''tempdb..#QueryMetadata'');
            '

    EXEC sp_executesql @sqlToExec

END

En 2019, ce devrait être la accepté de répondre!!!
Une très élégante solution.

InformationsquelleAutor Paul M Sorauer

12

Pour SQL Server 2012 et au-dessus: Si vous placez la requête dans une chaîne de caractères, alors vous pouvez obtenir le résultat de définir des types de données comme suit:
```
DECLARE @query nvarchar(max) = 'select 12.1 /10.1 AS [Column1]';
EXEC sp_describe_first_result_set @query, null, 0;  
```
- En 2019, ce qui devrait être accepté comme réponse!!!
InformationsquelleAutor redcalx

SELECT COLUMN_NAME,
       DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'

Vous pouvez utiliser les colonnes des alias pour mieux la recherche de la sortie.

InformationsquelleAutor Pawel Czapski

6

Pouvez-vous sortir avec de recréer la table intermédiaire à partir de zéro à chaque fois que la requête est exécutée? Si oui, vous pourriez utiliser SELECT ... INTO syntaxe et laissez SQL Server préoccuper de la création de la table en utilisant le bon type de colonne, etc.
```
SELECT *
INTO your_staging_table
FROM enormous_collection_of_views_tables_etc
```
- Malheureusement pas une option.
- Il pourrait également créer cette table intermédiaire juste pour obtenir les colonnes de types de données, à l'aide de la table information_schema pointé par erikkallen.
- Comment sur l'exécution de l' SELECT ... INTO ... FROM ... WHERE 1=0 syntaxe comme un one-off pour créer une table factice, puis la Gestion de l'utilisation du Studio de faire un script de création de la table SQL pour le mannequin de la table?
- Ceci est très utile lorsque la source de données est un tableau de valeur de la fonction, dans ce cas, vous ne pouvez pas utiliser information_schema.columns. Merci
InformationsquelleAutor LukeH

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='yourTable';

InformationsquelleAutor bop

Cela vous donnera tout de colonne liés à la propriété.

SELECT * INTO TMP1
FROM ( SELECT TOP 1 /* rest of your query expression here */ );

SELECT o.name AS obj_name, TYPE_NAME(c.user_type_id) AS type_name, c.*  
FROM sys.objects AS o   
JOIN sys.columns AS c  ON o.object_id = c.object_id  
WHERE o.name = 'TMP1';

DROP TABLE TMP1;

InformationsquelleAutor

1

sp_describe_first_result_set

aidera à identifier les types de données de la requête par l'analyse des types de données de premier jeu de résultats de la requête

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql?view=sql-server-2017

InformationsquelleAutor Alias Varghese

Vous devez vous connecter pour publier un commentaire.