Polecenie SQL do uzyskania typu kolumny
Czy istnieje polecenie SQL, które może zwrócić typ kolumny w tabeli?
16 answers
Using SQL Server:
SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'yourTableName' AND
COLUMN_NAME = 'yourColumnName'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-15 20:31:00
Najprostszym sposobem w TSQL jest:
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableName'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-15 22:54:26
Dla SQL Server, Ta systemowa procedura składowana zwróci wszystkie informacje o tabelach, w tym typy danych kolumn:
exec sp_help YOURTABLENAME
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-09-09 15:02:14
W TSQL / MSSQL wygląda to tak:
SELECT t.name, c.name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types y ON y.system_type_id = c.system_type_id
WHERE t.name = ''
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-15 20:27:13
W oracle SQL zrobiłbyś to:
SELECT
DATA_TYPE
FROM
all_tab_columns
WHERE
table_name = 'TABLE NAME' -- in uppercase
AND column_name = 'COLUMN NAME' -- in uppercase
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-09-08 12:36:40
Jeśli używasz MySQL możesz spróbować
SHOW COLUMNS FROM `tbl_name`;
Pokaż kolumny na dev.mysql.com
W przeciwnym razie powinieneś być w stanie zrobić
DESCRIBE `tbl_name`;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-15 20:34:12
USE [YourDatabaseName]
GO
SELECT column_name 'Column Name',
data_type 'Data Type'
FROM information_schema.columns
WHERE table_name = 'YourTableName'
GO
Zwróci nazwę kolumny values, pokazując nazwy kolumn i typy danych tych kolumn (int, varchars, itd.).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-15 22:53:06
Dla IBM DB2:
SELECT TYPENAME FROM SYSCAT.COLUMNS WHERE TABSCHEMA='your_schema_name' AND TABNAME='your_table_name' AND COLNAME='your_column_name'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-02-14 07:13:17
Kolejna odmiana używająca MS SQL:
SELECT TYPE_NAME(system_type_id)
FROM sys.columns
WHERE name = 'column_name'
AND [object_id] = OBJECT_ID('[dbo].[table_name]');
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-10-18 13:06:23
Using TSQL / MSSQL
To zapytanie dostarczy Ci: nazwę tabeli, nazwę kolumny, typ danych, Długość typu danych i dopuszczalne wartości Null
SELECT TABLE_NAME,COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name'
Jedyną rzeczą, która wymaga zmiany, jest nazwa_table_name.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-01-11 17:22:03
W moim przypadku musiałem uzyskać typ danych dla dynamicznego SQL (Shudder!) w każdym razie tutaj jest funkcja, którą stworzyłem, która zwraca pełny typ danych. Na przykład zamiast zwracać 'decimal' zwróci DECIMAL (18,4): dbo.GetLiteralDataType
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:18:16
Użyj tego zapytania, aby uzyskać schemat, tabelę, kolumnę, Typ, max_length, is_nullable
SELECT QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'Schema'
,QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'Table'
,C.NAME as 'Column'
,T.name AS 'Type'
,C.max_length
,C.is_nullable
FROM SYS.COLUMNS C INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id]
INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id
WHERE tb.[is_ms_shipped] = 0
ORDER BY tb.[Name]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-05-10 17:26:58
Używanie TSQL / MSSQL
Możesz użyć słowa kluczowego INTO
.
Wynik SELECT
do rzeczywistej tabeli
Przykład: select .... INTO real_table_name
Po
sp_help real_table_name
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-05-04 08:28:39
Dla Spark SQL:
DESCRIBE [db_name.]table_name column_name
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-02-23 18:15:53
Dla Apache Derby Jak pokazano w Ta odpowiedź :
select columndatatype from sys.syscolumns
where referenceid = (
select tableid from sys.systables
where tablename = 'YOUR_TABEL_NAME'
and columnname= 'YOUR_COLUMN_NAME')
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-08 15:07:46
SHOW COLUMNS FROM //table_name// ;
Wyświetli informacje o wszystkich kolumnach z tabeli .
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-29 09:54:09