Получить все таблицы в базе, в которых есть колонка с определенным названием
Есть база данных SQL Server 2008 с большим количеством таблиц. Нужно получить список всех таблиц БД, в которых есть колонка с названием Recorder_TYPE .
Отслеживать
8,657 18 18 золотых знаков 73 73 серебряных знака 181 181 бронзовый знак
задан 17 апр 2012 в 4:56
23 1 1 золотой знак 1 1 серебряный знак 3 3 бронзовых знака
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
select table_name from information_schema.columns where column_name='Recorder_TYPE' Отслеживать
ответ дан 17 апр 2012 в 5:25
11.5k 16 16 серебряных знаков 16 16 бронзовых знаков
select [Sch].[name] as [Schema], [Tab].[name] as [Table], [Col].[Name] as [Column] from sys.tables as [Tab] inner join sys.columns as [Col] on [Tab].[object_id] = [Col].[object_id] inner join sys.schemas as [Sch] on [Tab].[schema_id] = [Sch].[schema_id] where [Col].[Name] = 'Recorder_TYPE'; В предыдущем примере в результате запроса будут получены также «несуществующие» временные таблицы View.
Отслеживать
1,517 8 8 серебряных знаков 16 16 бронзовых знаков
ответ дан 24 мар 2016 в 3:02
SQL — Урок 4. Выборка данных — оператор SELECT
Итак, в нашей БД forum есть три таблицы: users (пользователи), topics (темы) и posts (сообщения). И мы хотим посмотреть, какие данные в них содержатся. Для этого в SQL существует оператор SELECT. Синтаксис его использования следующий:
SELECT что_выбрать FROM откуда_выбрать;
Вместо «что_выбрать» мы должны указать либо имя столбца, значения которого хотим увидеть, либо имена нескольких столбцов через запятую, либо символ звездочки (*), означающий выбор всех столбцов таблицы. Вместо «откуда_выбрать» следует указать имя таблицы.
Давайте сначала посмотрим все столбцы из таблицы users:
SELECT * FROM users;
Вот и все наши данные, которые мы вносили в эту таблицу. Но предположим, что мы хотим посмотреть только столбец id_user (например, в прошлом уроке, нам надо было для заполнения таблицы topics (темы) знать, какие id_user есть в таблице users). Для этого в запросе мы укажем имя этого столбца:
SELECT id_user FROM users;
Ну, а если мы захотим посмотреть, например, имена и e-mail наших пользователей, то мы перечислим интересующие столбцы через запятую:
SELECT name, email FROM users;
Аналогично, вы можете посмотреть, какие данные содержат и другие наши таблицы. Давайте посмотрим, какие у нас существуют темы:
SELECT * FROM topics;
Сейчас у нас всего 4 темы, а если их будет 100? Хотелось бы, чтобы они выводились, например, по алфавиту. Для этого в SQL существует ключевое слово ORDER BY после которого указывается имя столбца по которому будет происходить сортировка. Синтаксис следующий:
SELECT имя_столбца FROM имя_таблицы ORDER BY имя_столбца_сортировки;
По умолчанию сортировка идет по возрастанию, но это можно изменить, добавив ключевое слово DESC
Теперь наши данные отсортированы в порядке по убыванию.
Сортировку можно производить сразу по нескольким столбцам. Например, следующий запрос отсортирует данные по столбцу topic_name, и если в этом столбце будет несколько одинаковых строк, то в столбце id_author будет осуществлена сортировка по убыванию:
Сравните результат с результатом предыдущего запроса.
Очень часто нам не нужна вся информация из таблицы. Например, мы хотим узнать, какие темы были созданы пользователем sveta (id=4). Для этого в SQL есть ключевое слово WHERE, синтаксис у такого запроса следующий:
SELECT имя_столбца FROM имя_таблицы WHERE условие;
Для нашего примера условием является идентификатор пользователя, т.е. нам нужны только те строки, в столбце id_author которых стоит 4 (идентификатор пользователя sveta):
SELECT * FROM topics WHERE id_author=4;
Или мы хотим узнать, кто создал тему «велосипеды»:
Конечно, было бы удобнее, чтобы вместо id автора, выводилось его имя, но имена хранятся в другой таблице. В последующих уроках мы узнаем, как выбирать данные из нескольких таблиц. А пока узнаем, какие условия можно задавать, используя ключевое слово WHERE.
SELECT * FROM topics WHERE id_author=4;
SELECT * FROM topics WHERE id_author>2;
SELECT * FROM topics WHERE id_author =2;
Онлайн-курс. Освойте востребованную профессию с зарплатой от 70 000 руб в месяц!
Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.
admin@site-do.ru
site-do.ru © 2009-2019 Копировать материалы сайта запрещено!
За нарушение авторских прав предусмотрена уголовная ответственность!
Телефон редакции +7(499)346-89-56
127051, г.Москва, Цветной бульвар, д. 11, стр. 6, офис 405.
Как найти таблицу в sql по имени
Насколько часто мы — разработчики и администраторы SQL Server — вынуждены перебирать объекты в Enterprise Manager или открывать панель объектов в Query Analyzer, пытаясь найти таблицу или представление и не имея практически никакой информации, кроме почти правильного имени, и единственный путь убедиться, что это именно нужный нам объект — взглянуть на его структуру или текст. Конечно, такие вещи могут происходить не каждый день, но время от времени случаются (возможно, такого не происходит в идеальной ситуации, когда все базы данных хорошо документированы и все имена объектов соответствуют четким соглашениям по наименованию без каких-либо исключений, и более того, когда сотрудники никогда не увольняются).
Более привлекательным способом поиска объекта SQL Server, такого, как таблица, процедура или триггер, является запрос к системной таблице sysobjects в локальной базе данных (конечно, необходимо представлять, в какой базе данных располагается нужный объект).
Select * From sysobjects Where name like ‘ClientInvoice%’
Выполнение этого запроса отобразит все объекты в текущей базе данных, чьи имена начинаются на «ClientInvoice». Если тип искомого объекта известен, тогда запрос может быть изменен, чтобы вывести только объекты этого типа, и чьи имена начинаются на «ClientInvoice». Такой вариант может вернуть гораздо меньший и более читабельный результирующий набор данных.
Select * From sysobjects Where xtype = ‘U’ And name like ‘ClientInvoice%’
— ‘U’ для пользовательской таблицы
Главным недостатком указанных методов является то, что таблица sysobjects относится к определенной базе данных. Если вы не знаете, какая база данных содержит объект, тогда запрос необходимо запускать во всех базах данных, чтобы найти объект.
Существует ли более простой путь написания запроса, который бы производил поиск во всех базах данных за один шаг, отыскивая определенный объекта и / или определенный тип объектов? Ответ — да, это можно сделать при помощи удобной процедуры sp_MSforeachdb.
Exec sp_MSforeachdb ‘Select * From . sysobjects where xtype= »U» And name like »ClientInvoice% »’
sp_MSforeachdb — это недокументированная (значит, неподдерживаемая) процедура, доступная и в SQL Server 7, и в SQL Server 2000. Она принимает один строковый параметр, которым в нашем случае является скриптом II, но есть одно важное отличие — если мы внимательно посмотрим на скрипт III, то увидим «From . sysobjects» вместо «From sysobjects» в скрипте II.
Почему? Это важно, потому что sp_MSforeachdb использует внутри себя динамический SQL, и «?» — это шаблон имени базы данных, который заменяется на имя каждой базы данных в цикле, т.е. процедура в цикле последовательно вызывает таблицу sysobjects в каждой базе данных. Предположим, что у нас есть n баз данных, и если мы не поставим «?», то sp_MSforeachdb вместо цикла по n базам данных будет производить поиск в таблице sysobjects текущей базы данных (той, в которой мы запускаем запрос) n раз.
Теперь, когда мы знаем, что «?» является шаблоном имени базы данных, почему бы не попробовать написать скрипт, который выдаст результирующий набор данных с именем базы данных, именем объекта и типом объекта.
— 1 часть
Declare @sqlstr nvarchar(200)
— 2 часть
/* Удаление временной таблицы, если она существует */
If Object_Id(‘tempdb..#tblDBObjects’) is Not Null
Drop table# tblDBObjects
/* Создание временной таблицы */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)
— 3 часть
/* Присвоение строкового значения переменной */
Select @sqlstr = ‘sp_msforeachdb »Insert tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects»’
/* Выполнение строки SQL */
Exec sp_executesql @sqlstr
— 4 часть
/* Выборка данных из временной таблицы */
Select * From #tblDBObjects Where name like ‘ClientInvoice%’
RETURN
1 часть скрипта объявляет переменную типа nvarchar, т.к. строка, выполняемая процедурой sp_executeSQL должна иметь тип nvarchar.
2 часть проверяет, существует ли временная таблица с именем tblDBObjects. Если такая таблица существует, то она удаляется. После этого эта временная таблица #tblDBObjects создается вновь. ‘#’ означает, что таблица должна быть временной, поэтому она создается в базе данных tempdb. Временная таблица автоматически удаляется, как только скрипт успешно завершает свою работу.
3 часть создает строку SQL, которая вставляет значения в таблицу #tblDBObjects из таблицы sysobjects каждой базы данных. Причиной использования этой строки и команды sp_ExecuteSQL является то, что она позволяет нам передать тип объекта в виде входного параметра, если мы захотим написать хранимую процедуру и передать имя объекта и тип объекта в виде входных параметров. Передача типов объектов уменьшит результирующий набор данных и может также ускорить выполнение операции в том случае, если приходится работать с множеством больших баз данных. Это будет рассмотрено в скрипте V.
4 часть: когда временная таблица заполнена, записи могут быть выбраны в любой момент.
Пример хранимой процедуры, которая может использоваться для поиска объектов:
Create PROC FindObject_usp (
@objname varchar(200) = Null
, @objtype varchar(20) = Null
)
As
Declare @sqlstr nvarchar(200)
— Вставляйте специальные символы (wildcards), если не требуется точный поиск.
— Set @objname = ‘%’ + @objname + ‘%’ — Лучше определять пользовательские специальные символы (custom wildcards) для входного параметра @objname
/* Удаление временной таблицы, если она существует */
If Object_Id(‘tempdb..#tblDBObjects’) is Not Null
Drop table #tblDBObjects
/* Создание временной таблицы */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)
Begin
If @objtype = ‘CHECK’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»C»»»’
If @objtype = ‘Default’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»D»»»’
If @objtype = ‘FOREIGN KEY’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»F»»»’
If @objtype = ‘Log’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»L»»»’
If @objtype = ‘Scalar function’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»FN»»»’
If @objtype = ‘Inlined table-function’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»IF»»»’
If @objtype = ‘Stored procedure’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»P»»»’
If @objtype = ‘PRIMARY KEY’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»PK»»»’
If @objtype = ‘Replication filter stored procedure’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»RF»»»’
If @objtype = ‘System table’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»S»»»’
If @objtype = ‘Table function’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»TF»»»’
If @objtype = ‘Trigger’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»TR»»»’ If @objtype = ‘User table’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»U»»»’
If @objtype = ‘UNIQUE constraint’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»UQ»»»’
If @objtype = ‘View’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»V»»»’
If @objtype = ‘Extended stored procedure’
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects where xtype = »»X»»»’
If (@objtype = ») Or (@objtype is Null)
Select @sqlstr = ‘sp_msforeachdb »Insert #tblDBObjects select »»?»» as DBName, name, xtype From . sysobjects»’
End
/* Выполнение строки SQL */
If (@sqlstr <> ») Or (@sqlstr is Not Null)
Exec sp_executesql @sqlstr
/* Если параметр @objname не передан, то результат все равно должен быть возвращен */
If (@objname = ») Or (@objname is Null)
Select * From #tblDBObjects
Else
Select * From #tblDBObjects Where objName like @objname
RETURN
Этот скрипт создает хранимую процедуру, которая принимает 2 необязательных параметра — @objname (имя объекта, который надо найти) и @objtype (тип объекта, который надо найти). Типы объектов и их аббревиатуры могут быть найдены в разделе помощи о таблице sysobjects). Хранимая процедура FindObject_usp создает строки SQL различного вида в зависимости от типов данных, т.е., в зависимости от параметра @objtype. Если параметр @objtype не передан, то процедура выбирает все объекты из таблицы sysobjects и вставляет их во временную таблицу #tblDBObjects. Очевидно, что в случае больших баз данных, если тип объекта известен, передача параметра @objtype приводит к гораздо более быстрому выполнению запроса. Когда таблица #tblDBObjects заполнена, из нее выбираются данные при помощи параметра @objname, в том числе с использованием специальных символов.
Мы можем выполнить процедуру FindObject_usp, например, чтобы найти объект типа Check (ограничение), чье имя начинается на ‘CK_B’:
Exec FindObject_usp ‘CK_B%’, ‘check’
Exec FindObject_usp1 ‘xp_%’, Null
Я предполагаю, что эта процедура будет использоваться администраторами баз данных при необходимости. Это, конечно, не то, что будет выполняться на серверах баз данных большую часть времени, но если производительность является проблемой, вы можете заменить использование временной таблицы табличной переменной. Как только процедура появится в базе данных dbadmin или другой, где хранятся скрипты администраторов баз данных, вы сможете использовать ее, что скорее всего сделает жизнь администратора намного проще.
Команда SQL для выборки из базы данных (SELECT, WHERE, LIKE, AND, OR)
Команда SELECT позволяет получить данные из базы. Существует возможность задать различные фильтры и лимиты на выборку. Попробуем привести несколько примеров SQL запросов с ограничением выборки в таблице USERS, в которой содержатся данные пользователей.
Условие WHERE в SQL запросе
Получение всех записей в таблице в одном запросе — это очень редкий случай в реальных проектах. Зачастую нужна либо одна запись, либо диапазон, к примеру из 10 или 100 записей либо отвечающее определённому условию. Такую выборку можно сделать с помощью команды условия WHERE в SQL запросе (слово WHERE переводится с английского как «ГДЕ»).
Сравнение (=, !=, , =)
Продемонстрируем это условие на ограничении выборки по ID пользователя. Приведём сразу несколько примеров запросов:
SELECT * FROM `USERS` WHERE `ID` = 2; SELECT * FROM `USERS` WHERE `ID` != 2; SELECT * FROM `USERS` WHERE `ID` < 2; SELECT * FROM `USERS` WHERE `ID` 2; SELECT * FROM `USERS` WHERE `ID` >= 2;Как можно догадаться по математическим символам в этих запросах, выборка ограничена по ID пользователя (по целому числу). При каждом условии может возвращаться разное количество строк из таблицы. К примеру, если указано «WHERE `ID` = 2», то вернётся только одна строка, потому что поле «ID» зачастую уникально (то есть у столбца установлено свойство «PRIMARY KEY»). Если в запросе есть символ неравенства «!=» или сравнения «<, >, headline» >Поиск подстроки (LIKE) и полное соответствие (=)
Знак равенства «=» можно использовать в SQL запросах не только для чисел, но и для строк. Представим что нам нужно получить выборку из базы, в которой будут содержаться данные о пользователе с именем «Мышь». Запрос получится такой:
SELECT * FROM `USERS` WHERE `NAME` = 'Мышь';В результате мы получим все строки, в которых в столбце имени пользователя «NAME» содержится строка «Мышь». Обратите внимание, что совпадение должно быть полным. То есть в выборку не попадут пользователи, имена которых «Мышь серая», «Мышь белая», «Мышь чёрная». Чтобы выбрать и этих пользователей, необходимо сделать текстовый поиск по значению столбца. Для этого используется команда «LIKE» (в переводе с английского этот предлог звучит как «ПОДОБНО», «ВРОДЕ» или «СЛОВНО»).
С помощью команды «LIKE» можно искать подстроку в столбце. Чтобы сделать это поставьте знак процента «%» с той стороны подстроки, с которой могут находиться другие символы. К примеру:
SELECT * FROM `USERS` WHERE `NAME` LIKE 'Мышь%';В результаты выборки попадёт не только пользователь с именем «Мышь», но и «Мышь серая», «Мышь белая», «Мышь чёрная». Если поставить знак процента ещё и до подстроки:
SELECT * FROM `USERS` WHERE `NAME` LIKE '%Мышь%';то в выборку попадут не только все предыдущие результаты, но и пользователь с именем «Большая мышь».
LIKE делает поиск независимо от регистра. То есть результаты от ‘%мышь%’ и ‘%МЫШЬ%’ будут одинаковыми.
Логика «и» (AND) и «или» (OR)
Бывают случаи, когда необходимо задать несколько ограничений, связанных логикой. К примеру, если надо выбрать пользователей с ID от 2 до 5, то можно использовать условие с «AND»:
SELECT * FROM `USERS` WHERE `ID` >= 2 AND `ID` < 5;Количество условий и "AND" неограниченно:
SELECT * FROM `USERS` WHERE `ID` >= 2 AND `ID` < 5 AND `NAME` LIKE 'Мышь%';Существует возможность использовать логику "ИЛИ" благодаря условию "OR". Продемонстрируем это:
SELECT * FROM `USERS` WHERE `ID` < 2 OR `ID` >5;С помощью круглых скобок ( ) можно группировать условия OR и AND:
SELECT * FROM `USERS` WHERE (`ID` >= 2 AND `ID` < 5) OR (`ID` >10 AND `NAME` LIKE 'Мышь%');Выбор определённых столбцов в SELECT
В этой статье во всех SELECT запросах к базе запрашивались все поля. Потому что после слова SELECT стояла звёздочка *. Но чем больше объём данных вы выборке, тем медленнее база данных возвращает ответ. Поэтому старайтесь запрашивать у базы только то, что будете использовать. К примеру, если нужно получить только ID пользователя и имя 'NAME', то перечислите эти поля через запятую после слова SELECT:
SELECT `ID`, `NAME` FROM `USERS` WHERE `ID` Базы данных сайтов не приспособлены к получению больших выборок. Быстрее всего они работают на объёмах до 100 строк. Если попробовать запросить 100 000 строк из базы и указать вместо конкретных полей *, то можно будет увидеть значительное падение производительности. А чем медленнее загружается ваш сайт, тем меньше посетителей на него будут заходить. Поэтому всегда старайтесь оптимизировать свои запросы к базе.